8

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.

MPękalski
  • 6,873
  • 4
  • 26
  • 36

2 Answers2

24

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:

enter image description here

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
6

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);"
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 2
    It works great, so I have upvoted your answer. But I will accept the answer by @Damien_The_Unbeliever, which gives exactly the same result as your solution, as his gives an explicit Access solution. In future it might be more helpful for other people who do not know SQL and want to use Access. Once more thanks for your time. – MPękalski Apr 13 '11 at 08:49
  • 1
    @MPękalski: Hmm - the SQL DDL is Standard SQL, so should work on *any* SQL product, and the VBA is "an explicit Access solution" that won't work on any SQL product but Access. Haven't I, then, given you the best of both worlds? ;) – onedaywhen Apr 13 '11 at 09:32
  • 3
    Yeah I know that it works. But if you consider an average Access user, then I would argue that he does not know much a lot about SQL. At least at the company for which I work for. Maybe I should have stressed that the GUI solution would be the one that suits the most. ;) – MPękalski Apr 13 '11 at 09:38