Unique on Compound Columns

Question

I am trying to ensure that the values in RESTAURANTID and TABLENUMBER, together, are Unique using PowerDesigner (12.5). I've tried creating an alternate key which resulted in the following in my .sql file:

create table TABLES
(
   TABLEID             int not null,
   RESTAURANTID         int not null,
   TABLENUMBER         int not null,
   primary key (TABLESID),
   key AK_mykey (RESTAURANTID, TABLENUMBER)
);

However with this, I can still enter identical values for RESTAURANTID and TABLENUMBER more than once.

I used this http://www.tek-tips.com/viewthread.cfm?qid=403554 to create the alternate key in PowerDesigner.

Would anyone know the proper way to achieve this in PowerDesigner?

Note: This isn't a duplicate of the question posted above as I'm looking for a way to achieve this in PowerDesigner without having to edit the generated sql file afterwards.

What you are looking for is a composite key constraint. Take a look at this SO post for more information about this kind of key http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — WorkSmarter, Mar 22 '15 at 06:20

score 0 · Answer 1 · answered Mar 22 '15 at 06:31

0

You are just adding a normal index. What you need is a unique index. Just replace key AK_mykey (RESTAURANTID, TABLENUMBER) with unique key AK_mykey (RESTAURANTID, TABLENUMBER) in your query and you are done.

answered Mar 22 '15 at 06:31

Lorenz Meyer

19,166
22
75
121

Thanks Lorenz. Would you know how to accomplish this only using `PowerDesigner` so I don't need to edit my generated `SQL` file afterwards? – Mar 22 '15 at 06:46
Sorry, but I never used PowerDesigner. I use phpmyadmin. – Lorenz Meyer Mar 22 '15 at 07:27

pascal · Accepted Answer · 2015-03-23T09:13:35.400

The unique property for keys (other than primary) in MySQL is stored as an extended attribute on the key.

You can modify it by displaying, and going to the MySQL tab in the Key properties dialog.

Or, in Model>Keys, you can use the Customize Columns and Filter button to show the Ext Unique (extended) property in the list of keys, so that you can set this unique property on several keys at once.

Or, you can create your own copy of the MySQL DBMS, and edit it. Under Profile>Key (using the right-click), add an event handler Initialize with the following Event Handler Script, so that each new key has ExtUnique set:

Function %Initialize%(obj)
   obj.setextendedattribute "ExtUnique",true
   %Initialize% = True
End Function

score 0 · Answer 3 · answered Dec 08 '17 at 11:20

In Power Designer:

Open the "TABLES" table properties (right click -> properties)
Keys tab
Insert row (name it to "AK_mykey")
Apply (previously inserted row saved)
On the "AK_mykey" row: right click -> properties
Columns tab
Add columns button
Select the required columns (RESTAURANTID, TABLENUMBER)
OK, OK, OK buttons

+1 You can check the result in table properties preview tab.

alter table TABLES
   add unique AK_mykey (RESTAURANTID, TABLENUMBER);

Unique on Compound Columns

3 Answers3