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.

Answer 1

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