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.

If we want to have column1 of table1 as a foreign key of table2, then column1 should be a key in table1.

So let us alter the deptt_<<your lab username>> table to have depid as primary key of that table:

%%sql
alter table deptt_<<your lab username>> add primary key (depid);

Now let us create table emp_<<your lab username>> with foreign key which references the depid of deptt_<<your lab username>> table.

%%sql
use retail_db;
drop table  if exists emp_<<your lab username>>;

create table emp_<<your lab username>>(
    empid int not null auto_increment,
    empname varchar(100) not null,
    depid int not null,
    mgrempid int,
    primary key ( empid ),
    foreign key (depid) references deptt_<<your lab username>>(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.

%%sql
show create table emp_<<your lab username>>;

Observe that a constrain named emp_<<your lab username>>_ibfk_1 is visible by running the above command. This constrain name is given by the MySQL itself.

Now if we want to drop that foreign key, we can do so using that constrain name, as follows:

%%sql
alter table emp_<<your lab username>> drop foreign key emp_<<your lab username>>_ibfk_1;

We can also create a foreign key after defining a table, as follows:

%%sql
alter table emp_<<your lab username>>
add constraint fk_emp_depid 
foreign key (depid) references deptt_<<your lab username>>(depid);

In the above, fk_emp_depid is a user named constraint. Let us again view the table:

%%sql
show create table emp_<<your lab username>>;

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.

%%sql
alter table emp_<<your lab username>>
add constraint fk_emp_mgrempid
foreign key (mgrempid) references emp_<<your lab username>>(empid);

Observe that the primary key empid is being self-referenced here.

Let us again view the table:

%%sql
show create table emp_<<your lab username>>;

Previous Index Next

SQL with Jupyter Notebook Tutorial

Constraint Keys on Tables - Foreign Key

XP

Loading comments...