Can we create multicolumn unique indexes on MS access databases?

We'd like to prevent record duplication in our MS access database using a multicolumn unique index. Because of how the data is sent (via network), duplicate data is sometimes received. The data source does not send a unique ID, so the simplest option is to prevent duplicate records being inserted.

According to Unique Index Design Guidelines:

With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

This is for SQL 2005开发者_运维百科 however, so I'm not sure it's possible using MS access.

I guess an alternative is to perhaps use the query (pseudo code):

insert into foobar (a, b, c) values ('x', 'y', 'z')
where (a <> 'x') and (b <> 'y') and (c <> 'z')

... but I feel like an index would be better.

Turns out you can create a multi-column unique index on an MS access database, but it's a little crazy if you want to do this via the GUI. There's also a limitation; you can only use 10 columns per index.

Anyway, here's how you create a multi-column unique index on an MS access database.

1. Open the table in design mode, and Design, select Indexes.
2. Create a new row and enter a value in the Index Name cell,
3. Choose the first column from the drop down menu.
4. Add a new row and leave the Index Name cell blank.
5. Choose the second column, and so on.

Here's what it should look like:

Open the table in design view in MS Access, select the three columns that you want to make into the unique index, and then click the little key on the toolbar. You cannot have null values in a primary key (set).

We can make multi data to be unique data without set them as primary key.

(Note: only 1 data in the table can be primary key)

Step to set the data value as unique data (for MS ACCESS 2007 - 2010)

1. Open selected table in Design View
2. Click (Highlight) the specific column/attribute that you wish to set as unique
3. At the bottom of the table you will see "Index Properties" for that specific column
4. Find "Indexed" column, currently the data in Indexed text box is "No", change the data by click at the end of text box, choose "Yes(No Duplicates)"

Really hopes this methods can helps all of you! :)

I had the problem Nick Bolton reported above. Setting 2 fields (Foo, Bar) as PK set Foo to unique, when I wanted only the combination of Foo + Bar to be unique.

The problem turned out to be that I had created a 1:1 relationship to another table, linking on Foo. I deleted the relationship, set up the 2-field PK the way I wanted, and then reinstated the relationship, and it works as desired.

A trick I found is that in order to get a 2 column primary key (in the parent table) to be a child table´s 2(FK)+n primary key is to FIRST CHOOSE the indexed attribute as FISRT key attribute and THEN the not indexed attribute as a SECOND key attribute in the "Modify Relations Dialog Box" @Relations Window.

It will serve as Unique key in Ms Access 2007/2010