Unique constraint on multiple fields in Access 200

Question

I have not found any answer regarding my question, all unique constraint questions did not involve MS Access.

The question is how to make a unique constraint on multpile fields in MS Access 2003 database?

If my table consists of columns id, A, B, C, D, E, F. I have an index on column id, but I would like to have a unique constraint set on both columns A and B. Hence, I may have a duplicate value in column A, provided the value in column B are different.

I want to stress that I am not interested in a workaround like creating new column with concatenated values from columns A and B, and creating a constraint on this column.

Answer 1

In ANSI-92 Query Mode, execute this SQL DDL:

ALTER TABLE MyTable ADD 
   CONSTRAINT MyTable__key
      UNIQUE (A, B);

Using VBA in Access:

CurrentProject.Connection.Execute _
    "ALTER TABLE MyTable ADD CONSTRAINT MyTable__key UNIQUE (A, B);"

Answer 2

If you want to do it through the UI, open the table in design view. Open the indexes window. Enter a new index name, identify column A, mark the index as Unique. Then add a second row below that one, don't specify the index name on that row, and identify column B. Close the indexes window and save the table.

You now have a unique index on columns A and B. E.g. my unique index on A & B is called Boris here: