How to create Composite Unique Index in MS Access

We usually use ‘Primary Key’ in the tables to make data unique and prevent from duplicate data get entered. But it will create problem when we already got a primary key (such as an AutoNumber field) in table and we again want to use it. Beside the primary key we have one another concept which is called as “Composite Unique Index”. Using Index one can prevents duplicate values from being entered into the combination of the columns.

In Access unique index limit is of ten columns.

Here is an example given to explain the concept of composite unique index.

Firstly, create a table with the fields. As shown in Fig: - 1.1.

How to create Composite Unique Index Fig-1.1

Fig-1.1

Now we will create a composite unique index on the columns defined in the table, On the toolbar there is “Design” tab. Click on the “Indexes” as shown in the Fig: -1.2.

How to create Composite Unique Index Fig-1.2

Fig-1.2

After clicking on ”Indexes” a popup will open, asking for the “Index Name”, “Field Name” and “Sort Order” as shown in Fig 1.3.

How to create Composite Unique Index Fig-1.3

Fig-1.3

In “Index Name” enters the name of the index; you want and select the fields. Set Index property “Unique” to “yes” as shown in Fig: - 1.4. Here the Composite unique Index is created for the two fields.

How to create Composite Unique Index Fig-1.4

Fig-1.4

Now when someone will enter same values in the unique Index fields of the table then it will make restriction and will show error regarding duplicate index as shown in Fig: - 1.5.

How to create Composite Unique Index Fig-1.5

Fig-1.5


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.