Primary Key Constraint in SQL Server

 The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.

primary key constraints are implemented as unique indexes. By default, SQL Server creates a primary key as a unique clustered  index, although it is not a requirement. We can have nonclustered primary keys, or we can even have tables with no primary keys at all.

A table can have one primary key and a primary key can consist of single or multiple columns.

Example:

CREATE TABLE Persons (

    ID int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int,

    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);

Since In this case the primary key has two or more columns, you must use the PRIMARY KEY constraint as a table constraint.

In this example either ID or LastName value can be duplicate but combination of the two columns can't be duplicate.

Following table is created with out primary key.

CREATE TABLE events_cat(

    event_id INT NOT NULL,

    event_name VARCHAR(255),

    start_date DATE NOT NULL,

    duration DEC(5,2)

);

To make the event_id column as the primary key, you use the following ALTER TABLE statement:

 ALTER TABLE events_cat ADD PRIMARY KEY(event_id);

 Note that if the events_cat table already has data, before promoting the event_id column as the primary key, you must ensure that the values in the event_id are unique.

Different types of SQL Server Constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint