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 a PRIMARY KEY. And another table that keeps the reference of the 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 a constraint name otherwise MySQL automatically generates a 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 used by MySQL:-

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

MYSQL fully supports 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

);

The 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);

Read Also:-

PRIMARY KEY CONSTRAINT
UNIQUE CONSTRAINT
CHECK CONSTRAINT
NOT NULL CONSTRAINT
DEFAULT CONSTRAINT
CREATE INDEX CONSTRAINT
AUTO_INCREMENT CONSTRAINT
INDEX CONSTRAINT
ENUM CONSTRAINT

For more detail about MySQL constraints please follow here

Share with:


2 thoughts on “MYSQL FOREIGN KEY CONSTRAINT

Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics