FOREIGN KEY

Foreign Key

CREATE TABLE IF NOT EXISTS `teacher` (
    `teacher_id` INT,
    `teacher_name` CHAR(50),
    `teacher_email` VARCHAR(50),
    PRIMARY KEY (`teacher_id`)
);

CREATE TABLE IF NOT EXISTS `student` (
    `student_id` INT,
    `student_name` CHAR(50),
    `student_email` VARCHAR(50),
    `supervisor_id` INT,
    PRIMARY KEY (`student_id`),
    FOREIGN KEY (`supervisor_id`) REFERENCES `teacher`(`teacher_id`)
);

SHOW INDEX FROM `student`;

Foreign Key using Constraint

CREATE TABLE IF NOT EXISTS `teacher` (
    `teacher_id` INT,
    `teacher_name` CHAR(50),
    `teacher_email` VARCHAR(50),
    PRIMARY KEY (`teacher_id`)
);

CREATE TABLE IF NOT EXISTS `student` (
    `student_id` INT,
    `student_name` CHAR(50),
    `student_email` VARCHAR(50),
    `supervisor_id` INT,
    PRIMARY KEY (`student_id`),
    CONSTRAINT `FK_supervisor_id` FOREIGN KEY (`supervisor_id`)
    REFERENCES `teacher`(`teacher_id`)
);

SHOW INDEX FROM `student`;

Foreign Key using Alter Table

CREATE TABLE IF NOT EXISTS `teacher` (
    `teacher_id` INT,
    `teacher_name` CHAR(50),
    `teacher_email` VARCHAR(50),
    PRIMARY KEY (`teacher_id`)
);

CREATE TABLE IF NOT EXISTS `student` (
    `student_id` INT,
    `student_name` CHAR(50),
    `student_email` VARCHAR(50),
    `supervisor_id` INT,
    PRIMARY KEY (`student_id`)
);

ALTER TABLE `student`
ADD FOREIGN KEY (`supervisor_id`)
REFERENCES `teacher`(`teacher_id`);

SHOW INDEX FROM `student`;

Foreign Key using Alter Table + Constraint

CREATE TABLE IF NOT EXISTS `teacher` (
    `teacher_id` INT,
    `teacher_name` CHAR(50),
    `teacher_email` VARCHAR(50),
    PRIMARY KEY (`teacher_id`)
);

CREATE TABLE IF NOT EXISTS `student` (
    `student_id` INT,
    `student_name` CHAR(50),
    `student_email` VARCHAR(50),
    `supervisor_id` INT,
    PRIMARY KEY (`student_id`)
);

ALTER TABLE `student`
ADD CONSTRAINT `FK_supervisor_id`
FOREIGN KEY (`supervisor_id`)
REFERENCES `teacher`(`teacher_id`);

SHOW INDEX FROM `student`;

Drop Foreign Key

ALTER TABLE `student`
DROP FOREIGN KEY `FK_supervisor_id`;

SHOW INDEX FROM `student`;