SQLServerLog.com

SQL PRIMARY KEY Constraint

by · Published · Updated

primaryKeySQL PRIMARY KEY constraint uniquely identifies each row in a database table. A primary key column cannot have NULL values. A table can have only one primary key with single or multiple fields. Primary key with multiple fields are called composite key.  Please note that primary keys must contain UNIQUE values. You can create or define primary key by T-SQL or SQL Server Management Studio. When you will create a primary key, it will automatically create corresponding index.

You can define primary key constraint while creating a table. Here we are creating a table OldStudents with PRIMARY KEY defined on RollNumber column as shown below.

CREATE TABLE OldStudents
(
RollNumber int NOT NULL PRIMARY KEY,
Title varchar(8),
FirstName varchar(50) NOT NULL,
MiddleName varchar(50),
LastName varchar(50),
Country varchar(50)
)

You can also define the PRIMARY KEY constraint on multiple columns with PRIMARY KEY naming convention as shown below. 

CREATE TABLE OldStudents
(
RollNumber int NOT NULL,
Title varchar(8),
FirstName varchar(50) NOT NULL,
MiddleName varchar(50),
LastName varchar(50),
Country varchar(50),
CONSTRAINT PK_OldStudents PRIMARY KEY (RollNumber, FirstName)
)

Here we have given name for primary key as PK_OldStudents and PRIMARY KEY columns are RollNumber and FirstName.

You can drop the PRIMARY KEY constraint as given below.

ALTER TABLE OldStudents
DROP CONSTRAINT PK_OldStudents

You can add the PRIMARY KEY column on an existing table where column is defined as NOT NULL.

ALTER TABLE OldStudents
ADD PRIMARY KEY (RollNumber)

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

You may also like...

Leave a Reply