User Tools

Site Tools


notes:sql:primarykey

PRIMARY KEY

Different types of primary keys:

-- Autoincrement unnamed primary key
BookID INT IDENTITY(1,1) PRIMARY KEY
 
-- Autoincrement named primary key
BookID INT IDENTITY(1,1) CONSTRAINT PK_Book PRIMARY KEY
 
-- Non-autoincrement primary key
BookID INT PRIMARY KEY
 
-- Clustered primary
FriendlyName NVARCHAR(500) NOT NULL CONSTRAINT PK_Placeholders
    PRIMARY KEY CLUSTERED (FriendlyName ASC) WITH (IGNORE_DUP_KEY = OFF)
 
-- Clustered primary key with specifying fillfactor
BookID INT IDENTITY NOT NULL CONSTRAINT PK_Customers 
    PRIMARY KEY CLUSTERED(BookID) WITH FILLFACTOR=70 ON 'PRIMARY'
 
-- Composite primary key (table-level)
CodeID INT NOT NULL DEFAULT 0,
SubCodeID INT NOT NULL DEFAULT 0,
CONSTRAINT PK_Codes PRIMARY KEY (CodeID, SubCodeID)

Add a primary key to an existing table (the primary key column PK_Book should be NOT NULL):

-- Example#1
ALTER TABLE Books ADD CONSTRAINT PK_Book PRIMARY KEY (BookId)
GO
-- Example#2
ALTER TABLE Books WITH NOCHECK ADD CONSTRAINT PK_Book PRIMARY KEY CLUSTERED (BookId)
GO
notes/sql/primarykey.txt · Last modified: 2015/06/24 by admin