How unique field allow duplicate values from different item brands

[PRAISE PHS]

Hi All,
Pls, kindly help me out with this:
I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.
For example, I want to be able to save information like:
1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.
Pls, a quick response would be appreciated.

 

[IanRyder]

Hi,

Assuming that the information that you want to save is being saved in individual fields in your table then what you need to do is to create an index that creates Unique values based on multiple fields.

To do this, go into SSMS and design your table. Once there, select Manage Indexes and Keys and Add a New Index. Once done follow this process:-

1) Change the name of the New index to what you want
2) Click in the Columns field
3) Click on the ellipses to the right of the columns field
4) This will open another window which will show the current field being indexed. Change this to whatever the first field should be in the index
5) Click the ComboBox BELOW that field and add additional fields as you need to create a composite index using as many fields as you need
6) Once finished, save the table and the composite index will be created

Hope that helps.

Cheers,

Ian

 

[PRAISE PHS]

Hi,

Assuming that the information that you want to save is being saved in individual fields in your table then what you need to do is to create an index that creates Unique values based on multiple fields.

To do this, go into SSMS and design your table. Once there, select Manage Indexes and Keys and Add a New Index. Once done follow this process:-

1) Change the name of the New index to what you want
2) Click in the Columns field
3) Click on the ellipses to the right of the columns field
4) This will open another window which will show the current field being indexed. Change this to whatever the first field should be in the index
5) Click the ComboBox BELOW that field and add additional fields as you need to create a composite index using as many fields as you need
6) Once finished, save the table and the composite index will be created

Hope that helps.

Cheers,

Ian

Thanks a lot Ryder for the quick response. You actually gave me what I wanted and I'm very grateful for that.

 

[PRAISE PHS]

Ryder, pls what if I have my fields on different tables in my DB, how do I initiate composite indexing?

 

[IanRyder]

Ryder, pls what if I have my fields on different tables in my DB, how do I initiate composite indexing?

Hi,

You can't. Indexes are specific to each individual table since the Index is built based upon the data contained only within the fields of that particular table.

Cheers,

Ian