Set multiple "primary keys" on one AccountID

Question

This seems like a simple problem. However, I'm blocked.

I have 6 Product ID's: 1001, 1002, 1003, 1004, 1005, 1006.

In a table, I also have a list of account IDs. Each Account ID can have one of many product IDs(1-6), but no duplicates.

How do I add a constraint to allow multiple AccountID's in a table that could up to 1-6 product ID's but none of the productID's per one account could be duplicated?

Example Table :

Good Table

Account_ID  ProductID
138777  100004
138777  100003
--------------
21252   100003
138778  100003
138779  100004
138780  100003
138781  100001
138782  100004

Bad table

Account_ID  ProductID
138777  100003
138777  100003
---------------
21252   100003
138778  100003
138779  100004
138780  100003
138781  100001
138782  100004

What's the most logical way of implementing this constraint?

It appears that a [unique constraint](https://learn.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sql-server-ver15) will meet your needs. Indexing may improve query performance and can be done both ways, i.e. both `Account_Id, Product_Id` and `Product_Id, Account_Id`. — HABO, Feb 12 '20 at 16:41

Suraj Kumar · Accepted Answer · 2020-02-12T16:30:38.327

You can create the composite primary key on the column Account_ID and ProductID.

You can learn from this SO post How can I define a composite primary key in SQL?

You can alter the table if already created to add the composite primary key as shown below.

ALTER TABLE < YourTableName > ADD CONSTRAINT [name of PK, e.g. PK_AccountProduct] PRIMARY KEY (
    Account_ID
    ,ProductID
    )

You can do it in the SQL Server management studio by opening the table in the design view and selecting both the columns and right-click and set the composite/primary key.

In SSMS it looks like as shown below:

Set multiple "primary keys" on one AccountID

1 Answers1