Primary key

Question

I had gone through many sites and got that we cannot create multiple primary keys in a single table. But we can create primary key on multiple columns like primary key(column 1,column 2), that is composite primary key.

Can anyone explain what is the drawback if a table contains multiple primary keys.

Answer 1

RDBMS industry standard: 1 PRIMARY KEY per table.

You can create multiple UNIQUE KEYs.

Both can be created with CLUSTERED (1 per table) and NONCLUSTERED index options.

PRIMARY KEY default is CLUSTERED unique index.

BOL: "You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table."

Link:  http://msdn.microsoft.com/en-us/library/ms191166(v=sql.105).aspx

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

Primary Key creates Unique Clustered index by default on the table which means the data will be logically sorted according the CI Key, so the table can be sorted in one way, thus you can have only one CI on the table,

In order no enforce uniqueness you are able to create UNIQUE non clustered index on the table as well. 

 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Simple Primary Key Description:

Primary keys are unique keys with clustered by default

The drawback:

You can ONLY create ONE clustered index in a table and that is why you are not allowed to create multiple primary keys in a table.

though you can always use unique key with not null attribute to emulate multiple primary key

and the created column with unique key and not null attribute can be used in a relationship between tables

Conclusion:

Practically there is no real drawback. :)

Answer 4

 Thanks for your valuable replies.

we can't create multiple primary key  Because of clustered index created on primary key column default 

If  i am creating primary key with non clustered index, then what will happen.

I have tried this scenario, but i get the same result, i can't create one more primary key.

why it's not allowing to create another Primary key?

Answer 5

Yes,  you can create PK with unique nci...But without ci the table is still 'considered' as a heap...

Primary Key is the logical implementation 

Index is the physical implementation.

You can have ONLY one  PK on the table.... But again , if you want to enforce uniqueness you are able to create UNIQUE Constraint on the table along with PK. 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 6

we can't create multiple primary key  Because of clustered index created on primary key column default 

If  i am creating primary key with non clustered index, then what will happen.

I have tried this scenario, but i get the same result, i can't create one more primary key.

why it's not allowing to create another Primary key?

Don't confuse the physical index attributes with the constraints.  Both primary key and unique constraints both allow you to specify one or more columns as unique.  Per the ANSI SQL standard, primary key columns must not be NULL and only one is allowed per table whereas unique constraint columns may allow nulls and a table can have multiple unique constraints.  NULL values are checked for unique constraint purposes in SQL Server.

Once you identify the constraints you need, the physical indexes needed to support the constraints can be either clustered or non-clustered according to your performance and tuning analysis.  Which one should be the clustered index depends on your query mix.  It is nearly always best to have a clustered index on each table instead of a heap (no clustered index).

To clarify what has been stated in this thread, the primary key index is clustered by default only if a clustered index does not already exist on the table; the primary key will be non-clustered by default of a clustered index already exists.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 7

Thanks for reply.

what is logical implementation and how it works?

Answer 8

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.

If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite PRIMARY KEY constraint for this table. This makes sure that that the combination of ProductID and VendorID is unique.

When you work with joins, PRIMARY KEY constraints relate one table to another. For example, to determine which vendors supply which products, you can use a three-way join between the Purchasing.Vendor table, the Production.Product table, and the Purchasing.ProductVendor table. Because ProductVendor contains both the ProductID and VendorID columns, both the Product table and the Vendor table can be accessed by their relationship to ProductVendor.

---

Many Thanks & Best Regards, Hua Min

Answer 9

As Kalman wrote, Primary Key is a default Unique Key.

By the definition of the word "default", it can be only one.

Answer 10

This is a historical part of SQL. The PRIMARY KEY concept was taken from sequential files that had to be sorted on some field (not column!) to work. The first SQLs were built on those systems, both physically and conceptually. Even Dr. Codd made this mistake. He corrected himself, but not before this became part of the language. A key is a key and you can declare as many as  you wish, but you have to use NOT NULL UNIQUE constraints. In fact, I have several tricks for multiple keys and overlapping keys in my books. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL