mistux Site Admin
![](images/avatars/gallery/Miscellaneous/pc_user.gif)
Joined: 25 Jun 2004 Posts: 1042 Location: South Bend, Indiana USA
|
Posted: Wed Sep 17, 2014 3:20 pm Post subject: How to create a composite unique index (not as a primary key |
|
|
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.
Create indexes in MS Access (Indexes button).
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.
Indexes window is opened.
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.
Select field name for creating composite unique index.
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.
Select second field name for creating composite unique index.
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.
Creating composite unique index.
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.
Specify Uniqueness for composite unique index.
Here is what it looks after all the values are entered. You can now close this window and save the table design.
Unique composite index is created.
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.
Test composite index with duplicate values for the two columns.
Happy Indexing!
Source: http://www.geeksengine.com/article/composite-unique-index-access.html |
|