[Solved] Problem with UNIQUE CONSTRAINT in SQL code

[johlson]

There is a table (t_Orchestra) within a database. I wish to prevent duplicate entries into the txtOrchestra_Name_(Original_Language) field. I have attempted to accomplish this with the following SQL code: ALTER TABLE "t_Orchestra" ADD CONSTRAINT Orch_Name_unique UNIQUE ("txtOrchestra_Name_(Original_Language)"). This fails with the message “Unexpected token: " in statement [ALTER TABLE]". Can someone explain what I am doing wrong? Any help would be greatly appreciated.

[Villeroy]

The documentation suggests this:

Code: Select all

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] UNIQUE (<column list>);

<names> are placeholders for actual names of tables, fields and aliases in double quotes.
[brackets] mean that something is optional. You can leave it out completely. The [] are not written
Anything written (round braces) refers to a single named item which could be a list of many named items as well. The () need to be written.

Simply replacing all <names> with your actual names in double-quotes I get this:

Code: Select all

ALTER TABLE "t_Orchestra" ADD CONSTRAINT "Orch_Name_unique" UNIQUE ( "txtOrchestra_Name_(Original_Language)" );

[MTP]

You can also use the GUI to create constraints. Open the table in edit mode, click on Tools->Index Design, click the top left icon in the popup window for "new index", name it, choose your column in the dropdown box, then make sure to check the box "Unique".

[johlson]

As usual, Villeroy was spot on in his solution. Many thanks. Thanks also to HTP, whose solution I did not test, but will keep in mind.