Primary Key in Sql
A primary key uniquely
identify all the records in table
In other word we can say
that, the combination of unique and not null constrains is known as the primary
key .
For instance we have a database of a collage with all the
students enrolled in a class and we want to find out 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 above example, we will have roll number/student id
as a primary key which will uniquely identify each record.
Primary key
on create 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 Student
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Student
DROP CONSTRAINT PK_Student;
Important point to Remember about Primary Key
- ·
Primary Key uniquely identify the all Record In table .
- ·
In a Table We can have Only One primary Key .
- ·
Primary Key must contain unique value .
- ·
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 .
Tags:
sql