Foreign Key
Create table command with foreign key
use retail_db
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
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);
Taking you to the next exercise in seconds...
Want to create exercises like this yourself? Click here.
No hints are availble for this assesment
Answer is not availble for this assesment
Loading comments...