SQL PRIMARY KEY


A primary key is a field in a table which uniquely identifies each row/record in a database table.

Primary keys must contain unique values.

A primary key column cannot have NULL values.

Example

CREATE TABLE student (stud_id INT PRIMARY KEY, stud_name VARCHAR(60) NOT NULL,)

Points to remember for primary key

Primary key enforces the entity integrity of the table.

Primary key always has unique data.

A primary key length cannot be exceeded than 900 bytes.

A primary key cannot have null value.

There can be no duplicate value for a primary key.

A table can contain only one primary key constraint.


Main Advantage of Primary key

The main advantage of this uniqueness is that we get fast access.


SQL primary key for one column

The following SQL command creates a PRIMARY KEY on the "s_id" column when the "students" table is created.

MySQL:

CREATE TABLE student  
(  
s_id int NOT NULL,  
name VARCHAR(255) NOT NULL,  
address VARCHAR(255),  
PRIMARY KEY (s_id)  
)  

SQL Server, Oracle, MS Access:

CREATE TABLE student 
(  
s_id int NOT NULL PRIMARY KEY,  
name VARCHAR(255) NOT NULL,  
address VARCHAR(255),  
)  

SQL primary key for multiple columns

MySQL, SQL Server, Oracle, MS Access:

CREATE TABLE student
(  
s_id int NOT NULL,  
name varchar (255) NOT NULL,  
address varchar (255),  
CONSTRAINT pk_stud_id PRIMARY KEY (s_id, name)  
)  

The above example there is only one PRIMARY KEY (pk_stud_id). However it is made up of two columns (S_Id and LastName).


DROP a PRIMARY KEY

DROP (remove) a primary key constraint.

ALTER TABLE student  
DROP CONSTRAINT s_id  

Share this article on