SQL Primary Key Constraints with Example

Primary Key in SQL - How To Use Primary Key in SQL

A primary key uniquely identifies all the records in the table
In other words, we can say that the combination of unique and not null constraints is known as the primary key.

Example of Primary Key

For instance, we have a database of a college with all the students enrolled in a class and we want to find out the details of a student whose name is Ram and whose roll number is 101. There is a possibility that we have another student with the same name and when we tell the database to fetch details based on that name, it won’t know whose details we are asking for. Therefore, we need something that uniquely identifies every record and does not confuse the database and that is where the PRIMARY KEY comes into the picture.

In the above example, we will have roll number/student id as a primary key which will uniquely identify each record.

How to Use Primary Key in SQL while Creating Table, Alter Table?

Primary key to creating a table

My SQL

CREATE TABLE Student ( ID int NOT NULL, LastName varchar(150) NOT NULL, FirstName varchar(155), Age int, PRIMARY KEY (ID) );

SQL Server / Oracle / MS Access

CREATE TABLE Student ( ID int NOT NULL PRIMARY KEY, LastName varchar(155) NOT NULL, FirstName varchar(155), Age int );

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Student ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Student PRIMARY KEY (ID,LastName) );

Primary key on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Student ADD PRIMARY KEY (Id) ;

How to Drop a PRIMARY KEY Constraint

My SQL

ALTER TABLE StudentDROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Student DROP CONSTRAINT PK_Student;

Important Point to Remember about Primary Key

· Primary Key uniquely identifies all records in the table.
· In a Table, We can have Only One primary Key.
· Primary Key must contain a unique value.
· The primary key can not be null.
· Primary key can not accept the duplicate value.
· Primary key is the combination of not null and Unique constraint.

Hope!!! The above Tutorial on " SQL Primary key Constraint" is helpful For you...

Team,

QA acharya