SQL PRIMARY KEY

SQL PRIMARY KEY

This tutorial willl explain about the PRIMARY KEY in SQL and how to use them with the examples.

Te PRIMARY KEY constraint is to identify rows with unique value.

The PRIMARY KEY constraint restricts value not being NULL and not having duplicate value (Same value cannot be entered more than once).

Primary Key Syntax

CREATE TABLE Users (
UserID INT,
Name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY (UserID)
);

Above code will create Users table and define the UserID field as primary key. Lets now try to execute the following scenario and see what happens;

1. Execute the below code which will fail UserID will not accept NULL value
INSERT INTO Users(UserID, Name) VALUES (NULL, "User1");

2. Execute the below code which will work successfully
INSERT INTO Users(UserID, Name) VALUES (1, "User1");

3. Execute the below code which will fail as we entered UserID 1 on the previous example. Primary key will not accept that and fails
INSERT INTO Users(UserID, Name) VALUES (1, "User2");

Primary Key With Multiple Columns

A primary key may also be made up of multiple columns. For example,

CREATE TABLE Contacts (
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY (FirstName, LastName)
);

Above code will create Contacts table and define the FirstName and LastName fields as primary key. Lets now try to execute the following scenario and see what happens;

1. Execute the below code which will fail UserID will not accept NULL value on any primary column
INSERT INTO Contacts(FirstName, LastName) VALUES (NULL, "LastName1");

2. Execute the below code which will fail UserID will not accept NULL value on any primary column
INSERT INTO Contacts(FirstName, LastName) VALUES ("FirstName1", NULL);

3. Execute the below code which will fail UserID will not accept NULL value on both primary column
INSERT INTO Contacts(FirstName, LastName) VALUES (NULL, NULL);

4. Execute the below code which will work successfully
INSERT INTO Contacts(FirstName, LastName) VALUES ("FirstName1", "LastName1");

5. Execute the below code which will fail as we entered UserID 1 on the previous example. Primary key will not accept that and fails
INSERT INTO Contacts(FirstName, LastName) VALUES ("FirstName1", "LastName1");

Drop/Add Primary Key

Lets create a table with the following statement first
CREATE TABLE Items (
ItemID INT,
Name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Items PRIMARY KEY (ItemID)
)

The below statement will remove the primary key constraint from Items Table
ALTER TABLE Items
DROP CONSTRAINT PK_Items

You can add primary constraint with the following statement
ALTER TABLE Items ADD CONSTRAINT PK_Items PRIMARY KEY (ItemID)

Drop/Add Primary Key With Multiple Columns

Lets create a table with the following statement first
CREATE TABLE ProjectDailyNotes (
ProjectID VARCHAR(50) NOT NULL,
NoteDate Datetime NOT NULL,
Note VARCHAR(250) NOT NULL,
CONSTRAINT PK_ProjectDailyNotes PRIMARY KEY (ProjectID, NoteDate)
)

The below statement will remove the primary key constraint from ProjectDailyNotes Table
ALTER TABLE ProjectDailyNotes
DROP CONSTRAINT PK_ProjectDailyNotes

You can add primary constraint with the following statement
ALTER TABLE ProjectDailyNotes ADD CONSTRAINT PK_ProjectDailyNotes PRIMARY KEY (ProjectID, NoteDate)