Geek Answers Handbook

Unique limit on multiple fields in Access 2003

I did not find the answer to my question, all the unique restriction problems did not concern MS Access.

The question is how to make a unique restriction on multilevel fields in the MS Access 2003 database?

If my table consists of columns id, A, B, C, D, E, F I have an index in the id column, but I would like to have a unique constraint for both columns A and B Therefore, I can have a duplicate value in column A if the value in column B is different.

I want to emphasize that I am not interested in a workaround, for example , creating a new column with concatenated values ​​from columns A and B and creating a restriction in this column.

+8
sql unique ms-access-2003 constraints
source share
2 answers

If you want to do this through the user interface, open the table in view mode. Open the index window. Enter a new index name, specify column A, mark the index as unique. Then add the second row below this, do not specify the index name in this row, and do not specify column B. Close the index window and save the table.

You now have a unique index for columns A and B. For example. my unique index on A and B is called Boris here:

enter image description here

+19
source share

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);"
+6
source share

More articles:


All Articles