views:

223

answers:

2

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.

+3  A: 

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).

Remou
The Null issue is crucial -- if any of the fields can be Null, then you can't enforce uniqueness on the index (because no two Nulls are ever equal, two records with 5 identical fields and a 6th field Null in both would not be considered duplicates).
David-W-Fenton
This answer is right. But somebody should tell Nick that what he's really looking for is a unique CONSTRAINT, not a unique index. If he creaes a multicolumn PK constraint, he'll get a multicolumn unique index along with it. But the index is just a tool. The logical feature he's looking for is the constraint.
Walter Mitty
@Walter Mitty I think you're right - see the comment on my answer.
nbolton
There is no way in Access via the UI to create a UNIQUE constraint -- it can be done only via DAO or DDL.
David-W-Fenton
+1  A: 

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:

alt text

nbolton
Hmm, for some reason this makes `Foo` unique on it's own right, as opposed to combined with `Foo`, `Bar`, etc. I need to allow `("a", "b", "c", "d")` only once, but also allow `("x", "b", "c", "d")` -- and this arrangement of indexes doesn't seem to allow this because the last 3 values already exist.
nbolton
@Remou gave you an easier way to do it, i.e., select multiple rows and hit the PK button on the toolbar. This seems pretty intuitive to me, as it's the GUI way to set up any primary key.
David-W-Fenton
@David-W-Fenton Yup, like Remou said.
nbolton
Ha, please discard my first comment! I just tried this again today and it works as expected. Not quite sure what I was doing wrong yesterday.
nbolton