SQL Primary,Unique & Foreign Key

SQL Primary,Unique & Foreign Key are an important matter in Mysql

and other  database like MS Access ,SQL Server ,  Oracle .

In this tutorial i will discuss about SQL Primary,Unique & Foreign statement ,their purpose , syntax  and suitable example.

Purpose of Primary key:

Primary Key constraint is used for uniquely identifies each record in a database table.

Properties of primary key:

1. primary key column cannot contain NULL values.

2. A Primary keys must contain unique values.

3. A  table can have only one  primary key.

Primary key declaration in MySQL:

Primary key declaration in MySQL at the time of table creation.

CREATE TABLE `teacher Info2` (
`TEACHER_ID` INT NOT NULL , `FIRST_NAME` VARCHAR( 20 ) NOT NULL , `MIDDLE_NAME` VARCHAR( 20 ) NOT NULL , `LAST_NAME` VARCHAR( 20 ) NOT NULL , PRIMARY KEY ( TEACHER_ID ) )

TEACHER INFO

Primary key declaration in SQL Server/ MS Access / Oracle :

Primary key declaration in SQL Server/ MS Access / Oracle at the time of table creation.

 CREATE TABLE `teacher Info2` (
`TEACHER_ID` INT NOT NULL PRIMARY KEY ,
`FIRST_NAME` VARCHAR( 20 ) NOT NULL ,
`MIDDLE_NAME` VARCHAR( 20 ) NOT NULL ,
`LAST_NAME` VARCHAR( 20 ) NOT NULL
)

output of the above code:

TEACHER INFO

Primary key declaration in MySQL after a Table is  created:

SQL syntax:  

Alter table tbl_name add primary key (tbl_clm)

Example:

Alter table tbl_student add primary key (Roll)

Purpose of UNIQUE  constraint:

  UNIQUE constraint is used for uniquely identifies each record in a database table.

Properties of UNIQUE Constraint :

1. UNIQUE  Constraint column can contain NULL values.

2. UNIQUE  Constraintprovide a guarantee for uniqueness for a column.

3. A  table can have  one or more   UNIQUE Constraint .

UNIQUE  Constraint declaration in MySQL:

UNIQUE  Constraint declaration in MySQL at the time of table creation.

CREATE TABLE `teacher InfO2` (

`TEACHER_ID` INT NOT NULL ,
`FIRST_NAME` VARCHAR( 20 ) NOT NULL ,
`MIDDLE_NAME` VARCHAR( 20 ) NOT NULL ,
`LAST_NAME` VARCHAR( 20 ) NOT NULL ,
UNIQUE (TEACHER_ID)
)

UNIQUE  Constraint declaration in SQL Server/ MS Access / Oracle :

UNIQUE Constraint declaration in SQL Server/ MS Access / Oracle at the time of table creation.

CREATE TABLE `teacher InfO2` (

`TEACHER_ID` INT NOT NULL UNIQUE,
`FIRST_NAME` VARCHAR( 20 ) NOT NULL ,
`MIDDLE_NAME` VARCHAR( 20 ) NOT NULL ,
`LAST_NAME` VARCHAR( 20 ) NOT NULL

)

UNIQUE Constraint  declaration in MySQL / SQL Server / Oracle / MS Access  after a Table is created:

SQL syntax:  

Alter table tbl_name add  UNIQUE (tbl_clm)

Example:

Alter table tbl_student add UNIQUE (Roll)

Purpose of FOREIGN KEY:

To points a primary key a foreign key is used

For example:

FOREIGN KEY in one table points to a PRIMARY KEY in another table.

FOREIGN KEY  Constraint declaration in MySQL:

FOREIGN KEY Constraint declaration in MySQL at the time of table creation.

create table student

(

std_Id int NOT NULL,

std_Name varchar(25) NOT NULL,

Dept_Id int,

primary key (std_Id),

FOREIGN KEY (Dept_Id) REFERENCES teacherinfo(TEACHER_ID)
)

Output of the above code:

FOREIGN KEY Constraint  declaration in MySQL / SQL Server / Oracle / MS Access :

create table student

(

std_Id int NOT NULL primary key,

std_Name varchar(25) NOT NULL,

Dept_Id int  FOREIGN KEY REFERENCES teacher info (TEACHER_ID))

FOREIGN KEY  declaration in MySQL / SQL Server / Oracle / MS Access  after a Table is created:

ALTER TABLE student
FOREIGN KEY (Dept_Id) REFERENCES teacherinfo(TEACHER_ID)

Related Post or You may like:

Like Our Facebook Page To Get Update