SQL Constraint FOREIGN KEY
SQL Constraint FOREIGN KEY
In this video, we will see how to add foreign Key to table with example.
PPT:
FOREIGN KEY – used to link to table together.
Syntax:
CREATE TABLE tbl2 (
col1 data_type
col2 data_type FOREIGN KEY REFERENCES tbl1 ( id_pk ),
... );
OR
ALTER TABLE tbl2
ADD FOREIGN KEY (col2) REFERENCES tbl1 ( id_pk )
SQL Query:
create table patient
( patient_id integer primary key,
name varchar(20),
age integer
)
select * from patient
insert into patient values (1,'A',18)
insert into patient values (2,'B',22)
insert into patient values (3,'C',34)
insert into patient values (4,'D',43)
insert into patient values (5,'E',25)
create table visit (
visit_id integer primary key,
patient_id integer foreign key references patient (patient_id) ,
test varchar(10)
)
insert into visit values (1,1,'POSITIVE')
insert into visit values (2,1,'NEGATIVE')
insert into visit values (3,2,'NEGATIVE')
insert into visit values (4,3,'NEGATIVE')
insert into visit values (5,4,'NEGATIVE')
insert into visit values (6,null,'NEGATIVE')
insert into visit values (6,7,'NEGATIVE')
select * from patient
select * from visit
--can not perform delete operation on parent table
delete from patient where patient_id = 1
delete from patient where name = 'A'
-- delete records from child table then it will allow deletion of records from parent table
delete from visit where patient_id =1
--can not update PK of parent table
update patient
set patient_id = 22 where name ='B'
--update records from child table then it will allow deletion of records from parent table
update patient
set age = 23 where patient_id =2
-- delete table
-- delete parent table
drop table patient
-- delete child table
drop table visit
create table patient
( patient_id integer primary key,
name varchar(20),
age integer
)
select * from patient
insert into patient values (1,'A',18)
insert into patient values (2,'B',22)
insert into patient values (3,'C',34)
insert into patient values (4,'D',43)
insert into patient values (5,'E',25)
create table visit (
visit_id integer primary key,
patient_id integer foreign key references patient (patient_id) ,
test varchar(10)
)
insert into visit values (1,1,'POSITIVE')
insert into visit values (2,1,'NEGATIVE')
insert into visit values (3,2,'NEGATIVE')
insert into visit values (4,3,'NEGATIVE')
insert into visit values (5,4,'NEGATIVE')
insert into visit values (6,null,'NEGATIVE')
insert into visit values (6,7,'NEGATIVE')
select * from patient
select * from visit
--can not perform delete operation on parent table
delete from patient where patient_id = 1
delete from patient where name = 'A'
-- delete records from child table then it will allow deletion of records from parent table
delete from visit where patient_id =1
--can not update PK of parent table
update patient
set patient_id = 22 where name ='B'
--update records from child table then it will allow deletion of records from parent table
update patient
set age = 23 where patient_id =2
-- delete table
-- delete parent table
drop table patient
-- delete child table
drop table visit