Unique constraint on multiple fields in Access 2003

sql unique ms-access-2003 constraints
MPękalski picture MPękalski · Apr 12, 2011 · Viewed 13.2k times · Source

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

Damien_The_Unbeliever picture Damien_The_Unbeliever · Apr 13, 2011

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

Related questions

How to select only the first rows for each unique value of a column

Let's say I have a table of customer addresses: CName | AddressLine ------------------------------- John Smith | 123 Nowheresville Jane Doe | 456 Evergreen Terrace John Smith | 999 Somewhereelse Joe Bloggs | 1 Second Ave In the table, one customer like John Smith can have multiple addresses. I need …

Read More
How can I do SELECT UNIQUE with LINQ?

I have a list like this: Red Red Brown Yellow Green Green Brown Red Orange I am trying to do a SELECT UNIQUE with LINQ, i.e. I want Red Brown Yellow Green Orange var uniqueColors = from dbo in database.…

Read More
SQL - select distinct only on one column

I have searched far and wide for an answer to this problem. I'm using a Microsoft SQL Server, suppose I have a table that looks like this: +--------+---------+-------------+-------------+ | ID | NUMBER | COUNTRY | LANG | +--------+---------+-------------+-------------+ | 1 | 3968 | UK | …

Read More