35

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.

MaDa
  • 10,511
  • 9
  • 46
  • 84
Nick Bolton
  • 38,276
  • 70
  • 174
  • 242
  • Why not give the tables in question a unique index? I don't see what this has to do specifically with C++, BTW. –  Jan 24 '10 at 16:14
  • Yeah, I wasn't sure if it was that relevant. We already have a unique index; but the source of data does not provide this. I will update my question. – Nick Bolton Jan 24 '10 at 16:35
  • Your unique index needs to be on the actual data. –  Jan 24 '10 at 16:44
  • The data comes from a piece of equipment that does not generate unique indices, just data -- are we on the same page, or do I not understand what you're saying? Please could you advise on my hash idea? – Nick Bolton Jan 24 '10 at 16:52
  • A unique index specifies that certain combinations of data in a row must be unique, or the data it cannot be added to the row - it has nothing to do with the source of the data. It sounds like you need to get a day's worth of database design consultancy for your app. –  Jan 24 '10 at 16:57
  • Is not a unique index for only 1 column? We need the entire row to be unique... Are you saying that we need to generate an ID in our code, or somehow in the DB? – Nick Bolton Jan 24 '10 at 17:43
  • 2
    No - any index can be created on more than one column - indexes would be pretty useless if that were not the case. This is why I say you need some help - you don't seem to understand the basics of database design. –  Jan 24 '10 at 18:07
  • 1
    Neil, please could you explain how to create an index on more than one column using MS access? This would be very helpful. – Nick Bolton Jan 24 '10 at 18:15
  • Sorry, I don't use access myself. –  Jan 24 '10 at 18:41
  • 1
    Aha, I believe the term I'm searching for is "multicolumn unique indexes". I've never used these before... Maybe this answer will be helpful: http://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes – Nick Bolton Jan 24 '10 at 18:48
  • What kind of data? What kind of equipment? Why can't some records be the same if they are coming from a piece of equipment? – Tony Toews Jan 24 '10 at 19:02

6 Answers6

57

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

Ilmari Karonen
  • 49,047
  • 9
  • 93
  • 153
Nick Bolton
  • 38,276
  • 70
  • 174
  • 242
  • 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. – Nick Bolton Jan 24 '10 at 22:41
  • @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 Jan 25 '10 at 00:44
  • @David-W-Fenton Yup, like Remou said. – Nick Bolton Jan 25 '10 at 10:45
  • 1
    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. – Nick Bolton Jan 25 '10 at 11:15
11

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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • 1
    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 Jan 25 '10 at 00:43
  • 5
    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 Jan 25 '10 at 07:43
  • @Walter Mitty I think you're right - see the comment on my answer. – Nick Bolton Jan 25 '10 at 10:44
  • 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 Jan 25 '10 at 20:27
  • 1
    This is how to create a Primary Key in MSAccess.... it is a unique index, but is not the way to just create a unique index on any fields. If you don't need a Primary Key for anything else, this can solve the problem for a single Unique index. But the answer by @Ilmari Karonen is much more to the point. – Mike M Nov 30 '15 at 06:15
  • Primary keys are used to uniquely identify your record. The intention of primary key is not to prevent duplication. Use Index Unique constrains as @Ilmari Karonen mentioned. – henryyao Feb 12 '16 at 20:00
0

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! :)

finazrosli
  • 19
  • 7
  • 4
    This works only for single columns. The question is about multiple columns. – Andre Nov 11 '15 at 07:52
  • @Andre451 you can use this method for multiple column Andre. Just set this method for every column you wish to set as unique. Have you try to set in more than one column? Because it's work on mine. – finazrosli Nov 12 '15 at 07:41
  • 4
    Yes, of course you can set an unique index for multiple columns separately. But the question is about setting a single unique index that covers multiple columns. – Andre Nov 12 '15 at 07:47
0

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.

Biggles
  • 1
0

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.

MSolorio
  • 1
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 17 '21 at 02:59
-1

Unique key

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

Md Shahriar
  • 2,072
  • 22
  • 11