What is a primary key?

What is a primary key? Is it a type of index? How do I create one in SQL Server?

asked Oct 29 '09 at 11:56 AM in Default

Steve Jones - Editor ♦♦
5.1k ● 74 ● 78 ● 82

I bet £50 that David P has something to say :)

Oct 29 '09 at 11:58 AM Matt Whitfield ♦♦

(comments are locked)

3 answers:

A PrimaryKey of a table uniquely identifies each record.

In SQLServer, when you create a Primary Key, it's implemented as a unique clustered index on the column or columns (if there's not already a clustered index on the table and you don't specify a unique nonclustered index).

Once inplace, it means that inserts or updates that would create non-unique records are rejected. In SQLServer, primary key column's cannot allow NULL values.

In the table designer, you can create one by highlighting the column or columns and clicking on the key symbol in the toolbar.

Or you can use TSQL's CREATE TABLE statement which is described here

answered Oct 29 '09 at 12:37 PM

JustABitOfCode
54 ● 1 ● 1 ● 2

No - Firstly, you don't create primary keys by using create index, you use ALTER TABLE ... ADD CONSTRAINT [name] PRIMARY KEY

Secondly, while the UI in SSMS defaults a primary key to a clustered one, there is no reason that a PK must be clustered.

Thirdly, a clustered primary key it is not a unique clustered index. It's a clustered primary key constraint. The two, while being operationally similar, are not the same thing.

Oct 29 '09 at 03:38 PM Matt Whitfield ♦♦

Sorry, my mistake, of course I meant the CREATE TABLE statement, not the CREATE INDEX statement. The syntax for that is here</>.

I agree, there is no reason that a PK must be clustered, that's why I said it's implemented like that unless you specify otherwise.

According to the CREATE TABLE documentation the PRIMARY KEY... "Is a constraint that enforces entity integrity for a specified column or columns through a unique index." So I believe I was right to say that SQLServer implements a PK as a unique index.

Nov 02 '09 at 08:22 AM JustABitOfCode

(comments are locked)

Strictly speaking a Primary Key is a CONSTRAINT, not an index.

However when you specify one, SQL Server implements the uniqueness by creating a UNIQUE INDEX for the columns that you have specified for the primary key. This differs from a unique constraint in the fact that the columns in a PK are not allowed to be nullable [whereas a unique constraint will allow one and only one null]

As already noted, by default SQL will attempt to make the PK a clustered index, but you can specify it to be non-clustered.

Having a PK allows a FOREIGN KEY to reference the table (although UNIQUE INDEX or UNIQUE CONSTRAINT also allow this)

You can only have one PK per table.

answered Oct 29 '09 at 12:48 PM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

A FOREIGN KEY can reference either a UNIQUE INDEX or a UNIQUE CONSTRAINT - it doesn't necessarily need to be a PK. Otherwise, +1.

Oct 29 '09 at 03:41 PM Matt Whitfield ♦♦

fair comment, I've 'adjusted' the text

Oct 29 '09 at 06:31 PM Kev Riley ♦♦

(comments are locked)

A primary key is any candidate key.
"PRIMARY KEY" is also the name of a constraint which may optionally be used to enforce uniqueness of some columns in a SQL table. This need not be the same thing as 1.