In This Article, You Will Know About SQL PRIMARY KEY Constraint.
Before moving ahead, let’s know a bit about the SQL ALTER TABLE Statement.
Table of Contents
The SQL PRIMARY KEY constraint identifies uniquely each row/record in the table.
A table can have only one PRIMARY KEY but can have single or multiple columns in the table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
PRIMARY KEY – CREATE TABLE
SQL Server –
Example: Use the PRIMARY KEY on the column Roll_No.
CREATE TABLE Student_Info (
Name char(10),
Class char(5),
Section char(1),
Roll_No int NOT NULL PRIMARY KEY
);
MySQL Server –
Example: Use the PRIMARY KEY on the column Roll_No.
CREATE TABLE Student_Info (
Name char(10),
Class char(5),
Section char(1),
Roll_No int NOT NULL,
PRIMARY KEY (Roll_No)
);
PRIMARY KEY on Multiple columns –
MySQL / SQL Server
Example: Use the PRIMARY KEY on the columns Name and Roll_No.
CREATE TABLE Student_Info (
Name char(10) NOT NULL,
Class char(5),
Section char(1),
Roll_No int NOT NULL,
CONSTRAINT Table_Constraint PRIMARY KEY (Name, Roll_No)
);
Note: In the above example, there is only one PRIMARY KEY called (Table_Constraint), however, this PRIMARY KEY is created from two columns called (Name, Roll_No).
SQL PRIMARY KEY – ALTER TABLE
Create a PRIMARY KEY after the table is created.
MySQL / SQL Server
Example: Use the PRIMARY KEY on the column Roll_No.
ALTER TABLE Student_Info
ADD PRIMARY KEY (Roll_No);
Note: The column (Roll_No) cannot contain NULL value and must be declared not empty as PRIMARY KEY is created from this column.
Create a PRIMARY KEY on multiple columns after the table is created.
MySQL / SQL Server
Example: Use the PRIMARY KEY on the columns Name and Roll_No.
ALTER TABLE Student_Info
ADD CONSTRAINT Table_Constraint PRIMARY KEY (Name, Roll_No);
Note: The columns (Name, Roll_No) cannot contain NULL value and must be declared not empty as PRIMARY KEY is created from these columns.
DROP a PRIMARY KEY Constraint
Use the DROP to remove or delete a PRIMARY KEY Constraint.
MySQL Server
Example: Use the DROP to remove the PRIMARY KEY constraint.
ALTER TABLE Student_Info
DROP PRIMARY KEY;
SQL Server –
Example: Use the DROP to remove the PRIMARY KEY constraint.
ALTER TABLE Student_Info
DROP CONSTRAINT Table_Constraint;
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on: