Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 904
Nick Bolton Avatar asked Jan 24 '10 16:01

Nick Bolton


People also ask

How do you create a unique index in Access?

Click the Field Name for the field that you want to index. Under Field Properties, click the General tab. In the Indexed property, click Yes (Duplicates OK) if you want to allow duplicates, or Yes (No Duplicates) to create a unique index. To save your changes, click Save on the Quick Access Toolbar, or press CTRL+S.

Do unique constraints create indexes?

Yes, absolutely. A unique constraint creates a unique index.

What is a unique index in Access?

You can prevent duplicate values in a field in an Access table by creating a unique index. A unique index is an index that requires that each value of the indexed field is unique.

Can a table have 2 unique indexes?

If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes. One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.


1 Answers

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:

multicolumn index in MS access

like image 141
Nick Bolton Avatar answered Oct 20 '22 20:10

Nick Bolton