Big Data with Spark - Flat 33% OFF   Offer ends in

SQL Tutorial

25 / 59
   

Constraint Keys on Tables - Foreign Key

Foreign Key

  • In a typical RDBMS, tables have parent/child relationships which are enforced by Referential Keys.
  • Each value in referencing column of child table must exist in the referenced column of parent table.

Create table command with foreign key

use mydb1
drop table emp;
create table emp(
empid int not null auto_increment,
empname varchar(100) not null,
depid int not null,
mgrempid int,
primary key ( empid ),
foreign key fk_emp_depid (depid) references deptt(depid) on delete restrict
);

ON DELETE RESTRICT -> It prevents delete from parent table if records are present in the child table.
Above example gives system named constraint.

Referential keys can also be added after the table creation if not added in the table creation statement.

show create table emp;
alter table emp drop foreign key emp_ibfk_1;

alter table emp
add constraint fk_emp_depid --user system named constraint
foreign key (depid) references deptt(depid);

Above example gives a user named constraint.

Self-referencing Foreign Key

It is a foreign key which refers to another column in same table.
For example, data of employees and their managers have to be kept in the same table as all are employees only at the end.
At the same time, to represent manager for each employee, we have a column 'mgrempid' which is employee id of the manager.
This column will refer to 'empid' of the same table which will have full details of the manager as an employees.

alter table emp
add constraint fk_emp_mgrempid
foreign key (mgrempid) references emp(empid);