Loading

MYSQL FOREIGN KEY CONSTRAINT

FOREIGN KEY is used to relate two or more tables together. Basically, it produces a parent-child relationship. The parent table consists of PRIMARY KEY. And another table that keeps the reference of parent table PRIMARY KEY. That referential PRIMARY KEY of another table is called FOREIGN KEY. That’s the reason FOREIGN KEY is also called REFERENCE KEY. So FOREIGN KEY maintains referential Integrity in MySQL.

We can define FOREIGN KEY either using CREATE TABLE statement or using ALTER TABLE statement.

General Syntax of FOREIGN KEY:-

[CONSTRAINT constraint_name]

FOREIGN KEY [foreign_key_name] (column_name, …)

REFERENCES parent_table(colunm_name,…)

[ON DELETE referenceOption]

[ON UPDATE referenceOption]

CONSTRAINT NAME:- We can define constraint name otherwise MySQL automatically generates constraint name.

column_name:- It is the FOREIGN KEY column name.

parent_table:- It is the name of the parent table, which consists of PRIMARY KEY, Whom the reference we will use as FOREIGN KEY.

referenceOption:-It specify how FOREIGN KEY maintains referential integrity between the parent table and child table by using the ON DELETE and ON UPDATE clause.

There are five referential options is used by MySQL:-

  1. CASCADE:- It used, When parent table specific row will update or delete then child table specific row will be automatically updated or deleted.
  2. SET NULL:- It used, When parent table specific row will update or delete then child table specific row will become NULL.
  3. RESTRICT:- If a matching row of parent and child table then MySQL does not allow any update or delete of parent table. It is the default action.
  4. NO ACTION:- It is similar to RESTRICT. But one difference is It check referential integrity.
  5. SET DEFAULT:- Basically InnoDB and NDB tables reject this action. But MySQL parser recognizes this action.

MYSQL fully support CASCADE, RESTRICT and SET NULL.

Example:-

CREATE TABLE teacher(

teacher_ID INT NOT NULL AUTO_INCREMENT,

name VARCHAR(300),

age INT(5)

);

CREATE TABLE class(

class_ID INT NOT NULL AUTO_INCREMENT,

teacher_ID INT(11),

stander VARCHAR(255),

INDEX par_ind(teacher_ID),

CONSTRAINT fk_teacher FOREIGN KEY(teacher_ID)

REFERENCES teacher(teacher_ID)

ON DELETE CASCADE

ON UPDATE CASCADE

);

FOREIGN KEY example for SET NULL action

CREATE TABLE class(

class_ID INT NOT NULL AUTO_INCREMENT,

teacher_ID INT(11),

stander VARCHAR(255),

INDEX par_ind(teacher_ID),

CONSTRAINT fk_teacher FOREIGN KEY(teacher_ID)

REFERENCES teacher(teacher_ID)

ON DELETE SET NULL

ON UPDATE SET NULL

);

The syntax for Drop FOREIGN KEY:-

ALTER TABLE class DROP FOREIGN KEY fk_teacher;

Syntax of Add FOREIGN KEY for a single column:-

ALTER TABLE class ADD FOREIGN KEY (teacher_ID) REFERENCES teacher (teacher_ID);

Syntax of Add FOREIGN KEY for multiple columns:-

ALTER TABLE class ADD CONSTRAINT fk_teacherid FOREIGN KEY (teacher_ID) REFERENCES teacher (teacher_ID);

Share with:


One thought on “MYSQL FOREIGN KEY CONSTRAINT

Leave a Reply

Connect with:





z35W7z4v9z8w