top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Can we have two primary keys in one table?

+2 votes
447 views
Can we have two primary keys in one table?
posted May 17, 2017 by Rohini Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

3 Answers

+1 vote

You ask if you can have more than one primary key field and you most certainly can. You can have only one primary key, but that can consist of as many columns as you need to uniquely identify your rows.

Use something like this when you are creating your table:

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
where P_Td and LastName are columns in your table.

If you think you want more than one primary key, then the answer is "not really." You can have only one primary key. However, you can have as many indexes as you want that have a unique constraint on them. A unique index does pretty much the same thing as a primary key.

for example :

CREATE TABLE Persons
(
   P_Id int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Address varchar(255),
   City varchar(255),
   CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).

answer May 29, 2017 by Manikandan J
+1 vote

You can only have one primary key, but you can have multiple columns in your primary key.

You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values.

answer Jul 1, 2017 by Ajay Kumar
0 votes

No You cannot have two primary keys in one table, but you can have composite primary key

Because Primary key is an identity to the row and there can't be two IDs against a row.

answer Apr 23, 2019 by Siddhi Patel
...