Custom Search

How to create a composite unique index (not as a primary key) in MS Access

This article applies to Microsoft Office Access 2003 and above.

It's a very useful practice that we sometimes need to store unique data across multiple columns in a table. One way of doing this is to create a primary key which contains these columns, but what if we already got a primary key (such as an AutoNumber field) in the table and we still want to keep and use it? The answer is to create a unique composite index on these columns. This type of index prevents duplicate values from being entered into the combination of these columns.

This article shows you the steps to create a unique composite index. Each composite unique index in Access can contain up to 10 columns.

The table used in this demo has three columns: ID, Product_Name, Category_ID.

We want to create a composite unique index on column Product_Name and Category_ID.

Column Name Date Type Description
ID AutoNumber Primary Key
Product_Name Text  
Category_ID Number  

We want to make sure there are no duplicate values in Product_Name column in the same category, so we need to define a composite unique index on column Product_Name and Category_ID. This way, when data are added to this table, Access Jet engine checks the data and prevents duplicate values from being entered into column Product_Name and Category_ID as a whole.

1. Open the table in Design View.

Open your Microsoft Access database. Click the Tables menu to see all the tables in the database. Find the table you want to add the index and right click on the table name, then select Design View.

2. Click Indexes button.

On the toolbar menu across the top, click the Indexes button to open the window that has the list of fields on which the table can be indexed.

3. Enter the first column for the index.

The Indexes window is opened as shown below. Note that the primary key column has already been added to the first row automatically.

In the second row of the indexes window, enter the Index Name as Uidx_Products (U stands for unique) and select Product_Name from the dropdown list for Field Name. Use Ascending as the Sort Order.

4. Enter the second column for the index.

Next, in the third row, don't enter anything in the Index Name field (leave it blank). In the Field Name, select Category_ID in the dropdown list and Use Ascending for Sort Order.

Here is what it looks after the third row is filled. If you have more than two columns for the composite unique index, repeat this step to add more columns for Field Name.

Each unique composite index in Access can contain up to 10 columns.

5. Specify the Unique property for the index.

Now it comes to the crucial part. Click the Index Name cell for Uidx_Products. Then, in the Index Properties section of the window, select Yes in the dropdown for the Unique property. This will enforce data uniqueness for the combination of column Product_Name and Category_ID.

Here is what it looks after all the values are entered. You can now close this window and save the table design.

6. Test the unique composite index.

Now open the table again but this time open in Datasheet view. Enter some values in the table to test. The screenshot below shows that I entered Chocolade twice for Category_ID 1 and Access prompted the error message because the last value I entered was a duplicated entry which is the same as the first value for Category_ID 1. You may notice that the third value is also Chocolade but Access accepted it because its Category_ID is 2.

Happy Indexing!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy