Big Data with Spark - Flat 33% OFF Offer ends in
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);