CODEWITHSUNDEEP
sqlprimary-keyunique-key
Eminem
Eminem

Reputation: 7484

SQL Primary Key

I came across the 2 versions of sql code.. 

--1
CREATE TABLE Location ( 
Id     INTEGER PRIMARY KEY 
                   NOT NULL,
Name   TEXT    NOT NULL 
);

--2
CREATE TABLE Location ( 
    Id     INTEGER PRIMARY KEY 
                       NOT NULL
                       UNIQUE,
    Name   TEXT    NOT NULL 
);

In SQL, is it necessary to specify the primary key to be unique and not null?
I always assumed that the primary key was unique and could not be null.

Upvotes: 2

Views: 942

Answers (4)

onedaywhen
onedaywhen

Reputation: 57093

Your code is valid syntax. It will create both a PRIMARY KEY and a UNIQUE constraint covering the same column.

There are legitimate reasons for a table having more than one key but not on the same set of columns. A table may only have one key flagged as "primary". Every table requires at least one key but there is no requirement to flag a key as "primary", even when a table has only one key.

In SQL Server, flagging as "primary" has implications (e.g. NOT NULL, the default key when creating a foreign key reference, etc) but I prefer to be explicit about such things. Presumably the intention is for your table to have a sole key so I suggest you omit the PRIMARY KEY. I further recommend you give your UNIQUE key an explicit name e.g. 

CREATE TABLE Location 
( 
 Id INTEGER NOT NULL 
    CONSTRAINT Location__key UNIQUE, 
 Name TEXT NOT NULL 
);

Upvotes: 2

Bastardo
Bastardo

Reputation: 4152

A table can have at most one primary key, but more than one unique key. A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not.

Upvotes: 4

Andriy M
Andriy M

Reputation: 77737

Both UNIQUE and NOT NULL are unnecessary, because PRIMARY KEY implies both.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332791

Specifying "UNIQUE" on a primary key column is redundant - being the primary key already ensures that will be the case.

Upvotes: 3

Related Questions