Get Started: SQL PRIMARY KEY Constraint

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:

Leave a Comment

Stay in the loop

codingstreets