What is a primary key?

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

more ▼

asked Oct 29, 2009 at 11:56 AM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

I bet £50 that David P has something to say :)
Oct 29, 2009 at 11:58 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Oct 29, 2009 at 12:37 PM

JustABitOfCode gravatar image

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, 2009 at 03:38 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 29, 2009 at 12:48 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

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, 2009 at 03:41 PM Matt Whitfield ♦♦
fair comment, I've 'adjusted' the text
Oct 29, 2009 at 06:31 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
  1. A primary key is any candidate key.

  2. "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.

more ▼

answered Oct 30, 2009 at 09:58 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 29, 2009 at 11:56 AM

Seen: 1695 times

Last Updated: Oct 29, 2009 at 11:56 AM