Can a table have multiple Primary keys?

Question

I am very confused right now, maybe you can help me to understand the problem better regarding the question that can a table have two primary keys if yes then how ? And if no, then why?

Answer 1

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).