Seeder question: I have heard that PRIMARY KEY and UNIQUE constraints can be used interchangeably - is this true? What is the difference between the two?
Here are the various points I know of:
You can only have one primary key per table, but multiple unique constraints (249, I believe)
When you create a unique constraint, SQL Server creates a Unique index. This is Non-Clustered by default, but it can be changed. When you create a primary key, a Unique Constraint Primary Key is created - you have the option to set
IGNORE_DUP_KEY. The PK is Clustered by default - this can be changed.
A PK can not have a NULL value - if you try to make a NULLABlE column as your PK, SQL Server will change it to NOT NULL first, and then create the PK. A Unique constraint can have one NULL value.
They can be used interchangeably up to a certain degree - it depends on how you intend to use it.
A primary key, as well as a unique constraint will uniquely identify a row in a given table. Each table, can have a single primary key, and multiple unique constraints. As Raj mentioned, a Primary Key cannot have a NULL value and a Unique Constraint can have a single NULL Value (Unless using a filtered index).
Both a Primary Key and a Unique Constraint, are implemented as indexes on the table. And, while SQL Server will by default create the PK as the clustered index, it isn't a requirement that the clustered index be the PK on the table, it is possible to create another index as the clustered index.
Additionally, you can create a UNIQUE CLUSTERED INDEX on the table, that while not technically a unique constraint can be the clustered index and not the PK.
Whereas, a UNIQUE Constraint is implemented as an Index, a Unique Index is not implemented as a constraint.
Often, unique constraints are referred to as ALTERNATE KEYS.
One other item of interest, is that either a PK or a Unique Constraint can be used as a Foreign Key....
CREATE TABLE TEST ( PK_COL INT NOT NULL ,AK_COL INT NOT NULL ,UC_COL INT NOT NULL ) ; ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY NONCLUSTERED (PK_COL) ; CREATE UNIQUE CLUSTERED INDEX CUI_TEST ON TEST ( AK_COL ); ALTER TABLE TEST ADD CONSTRAINT UC_TEST UNIQUE (UC_COL) ; EXEC sp_helpindex TEST ; EXEC sp_helpconstraint TEST ; DROP TABLE TEST ;
You can only have one primary key on a table but using unique constraints you can enforce uniqueness on more columns (or combinations of columns)
A standard setup for a user table is to have the primary key set to a user id or user name and have a unique contraint on their email address.
I think Raj has a complete answer, but I'll add one thing. A Primary Key is a unique constraint, but a unique constraint is not a primary key. A unique constraint is a superset of primary keys, or PKs a subset of the unique constraints.
One difference that hasn't been mentioned yet is that a PRIMARY KEY constraint will be the default target for a FOREIGN KEY constraint referencing that table if you create the FOREIGN KEY without specifying a column list. Otherwise PRIMARY KEY achieves the same thing as a UNIQUE constraint on NOT NULL columns.
It is a very widely observed convention of SQL database design that every table should have a PRIMARY KEY constraint. This is of little practical consequence however and it would be quite possible (and very possibly desirable) to do without PRIMARY KEY constraints altogether.
A primary key is a unique field on a table but it is special in the sense that the table considers that row as its key. This means that other tables can use this field to create foreign key relationships to themselves.
A unique constraint simply means that a particular field must be unique.
1 Person is following this question.