CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasesql-server-2008
Pankaj Kumar
Pankaj Kumar

Reputation: 685

Can a table have multiple Primary keys?

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?

Upvotes: 32

Views: 146712

Answers (10)

Nagaraj S
Nagaraj S

Reputation: 13484

As you cannot define more than one column as Primary Key as below

create table test1 (col1 numeric(10) primary key, col2 numeric(10) primary key 
,col3 numeric(10) primary key, col4 numeric(10))

It needs to be a composite key. Yes, we can have more than one column as primary key to solve some business requirements. Primary Keys ensures that the column(s) will not have duplicate values , Null in the table.

Below is the SQL to create a table with Composite Primary Key

CREATE TABLE track( 
col1 numeric(10) , col2 numeric(10) ,col3 numeric(10), col4 numeric(10), 
PRIMARY KEY (col1,col2,col3) 
)

Upvotes: 1

Arm Avi
Arm Avi

Reputation: 21

You Can try FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

Upvotes: 1

Bha15
Bha15

Reputation: 271

No.The table have only on primary key. But that primary key can contain multiple field. Means when you create table and when you mention primary key, you can add more then one column which you want to. for example

CREATE TABLE table_name ( col1 Datatype , col2 Datatype,col3 Datatype, col4 Datatype, PRIMARY KEY (col1,col2,col3) )

By this way you can add primary key in single table

Upvotes: 5

harish
harish

Reputation: 11

CREATE TABLE track( col1 numeric(10) , col2 numeric(10) ,col3 numeric(10) , col4 numeric(10), PRIMARY KEY (col1,col2,col3) )

Upvotes: 1

Diwakar
Diwakar

Reputation: 517

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

Upvotes: 51

nvogel
nvogel

Reputation: 25534

A table can have many keys but by convention only one key per table is designated a "primary" one. Typically this may be the key deemed to be the "preferred" identifier for the information in that table or it may be a key singled out for some other specific purpose by the table's designer.

In principle whatever function or property you associate with the key designated "primary" could just as well be associated with any other key as well. Therefore for many practical purposes you could designate more than one such "primary" key if you so choose - but only if the limitations of any particular DBMS permit.

Under the relational model of data all keys are equal and there is no special function given to primary keys (in fact the relational usage of the term primary key originally referred to any and all keys of a relation and not just one key). Unfortunately many DBMSs don't respect this principle and may limit certain features to one and only one key of a table, making it necessary to be selective about which key gets designated as primary. So the answer to your question ought to be YES in principle. When you need to achieve it in some particular SQL DBMS the actual answer is: it depends.

Upvotes: 7

Niels Keurentjes
Niels Keurentjes

Reputation: 41968

On a table you can make indexes, which allow the internal database engine to process the contents of the affected columns (1 to many) for easy lookup. Because the engine is at that point already evaluating and sorting the contents of the fields, it can also easily ensure uniqueness of the values. Thus an index can span 1 to many rows, and optionally also be unique.

A primary key is a theoretically optional, though in practice mandatory, marker on a specific index that it's the eternally unique way of referencing a specific row in the table. It's usually either a GUID or an auto-increment integer (identity in SQL Server). The primary key itself is unique for any given table, and enforces a unique constraint by definition, but can optionally span multiple rows (a spanned index/key).

You could for example have a junction table containing only 2 fields, which are both foreign keys, and together form the primary key/index of the table.

Upvotes: 1

Vaibhav Parmar
Vaibhav Parmar

Reputation: 643

No.You cannot use more than 1 primary key in the table.for that you have composite key which is combination of multiple fields.

Upvotes: 0

Andrew
Andrew

Reputation: 27314

You can only have 1 primary key - the range of keys that could all potentially be the primary key can be referred to as candidate keys. The one you select is the primary key, the other alternative keys can be implemented as unique constraints / indexes.

So whilst there is only 1 primary key, you can still ensure primality of other fields / combination of fields using the unique constraint / index.

Upvotes: 3

zzlalani
zzlalani

Reputation: 24384

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.

Upvotes: 20

Related Questions