Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Enforcing the Foreign Key Constraint
SQL>
SQL>
SQL> The context for this example is a small family counseling practice where we will need to keep track of information
SQL>
SQL> counselors, clients, and appointments for counseling.
SQL>
SQL>
SQL> In this example, we will schedule counseling sessions where a client will be counseled by a counselor at a specific date
SQL>
SQL> and time. We want to be sure that the client specified for the appointment is really a client of the practice.
SQL>
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> create table client(
2 CLIENT_ID int primary key,
3 FIRST_NAME varchar(20),
4 LAST_NAME varchar(20),
5 PHONE varchar(14)
6 );
Table created.
SQL> insert into client values(100,'Ashley','Lloyd','(508) 759-1317');
1 row created.
SQL> insert into client values(101,'Sandra','Welch','(508) 786-0586');
1 row created.
SQL> insert into client values(102,'Clive','Sanford','(508) 297-2070');
1 row created.
SQL> insert into client values(103,'Abby','Harris',null);
1 row created.
SQL> insert into client values(104,'Gene','Mann','(508) 478-4373');
1 row created.
SQL> insert into client values(105,'Jennifer','Marin','(508) 867-5309');
1 row created.
SQL> create table appointment(
2 APPT_ID int primary key,
3 COUNSELOR varchar(20),
4 CLIENT_ID int,
5 ROOM number(1,0),
6 START_TIME date,
7 DURATION number(3,0)
8 );
Table created.
SQL> insert into appointment values(1,'Quinn Fowler',100,1,to_date('2016-2-15 1:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(2,'Quinn Fowler',101,1,to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(3,'Quinn Fowler',102,1, to_date('2016-2-15 3:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(4,'Elizabeth Baker',105,2, to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),115);
1 row created.
SQL> select * from client;
CLIENT_ID FIRST_NAME LAST_NAME PHONE
---------- -------------------- -------------------- --------------
100 Ashley Lloyd (508) 759-1317
101 Sandra Welch (508) 786-0586
102 Clive Sanford (508) 297-2070
103 Abby Harris
104 Gene Mann (508) 478-4373
105 Jennifer Marin (508) 867-5309
6 rows selected.
SQL> select * from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
SQL> /**************************************************************************************************************************
SQL>
SQL> Add an appointment with the following information:
SQL>
SQL> APPT_ID: 5
SQL> COUNSELOR: Elizabeth Baker
SQL> CLIENT_ID: 107
SQL> ROOM: 2
SQL> Date: 2-FEB-2016
SQL> TIME: 10:00 am
SQL> DURATION: 55 minutes
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL>
SQL> insert into appointment values(5,'Elizabeth Baker',107,2, to_date('2016-2-15 10:00 am','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> select * from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
5 Elizabeth Baker 107 2 15-FEB-2016 10:00 AM 55
SQL> drop table appointment;
Table dropped.
SQL> create table appointment(
2 APPT_ID int primary key,
3 COUNSELOR varchar(20),
4 CLIENT_ID int references Client,
5 ROOM number(1,0),
6 START_TIME date,
7 DURATION number(3,0)
8 );
Table created.
SQL> insert into appointment values(1,'Quinn Fowler',100,1,to_date('2016-2-15 1:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(2,'Quinn Fowler',101,1,to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(3,'Quinn Fowler',102,1, to_date('2016-2-15 3:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(4,'Elizabeth Baker',105,2, to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),115);
1 row created.
SQL> select * from client;
CLIENT_ID FIRST_NAME LAST_NAME PHONE
---------- -------------------- -------------------- --------------
100 Ashley Lloyd (508) 759-1317
101 Sandra Welch (508) 786-0586
102 Clive Sanford (508) 297-2070
103 Abby Harris
104 Gene Mann (508) 478-4373
105 Jennifer Marin (508) 867-5309
6 rows selected.
SQL> select * from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
SQL> insert into appointment values(5,'Elizabeth Baker',107,2, to_date('2016-2-15 10:00 am','yyyy-mm-dd hh:mi am'),55);
insert into appointment values(5,'Elizabeth Baker',107,2, to_date('2016-2-15 10:00 am','yyyy-mm-dd hh:mi am'),55)
*
ERROR at line 1:
ORA-02291: integrity constraint (GOVE.SYS_C007199) violated - parent key not found
SQL> insert into appointment values(5,'Elizabeth Baker',104,2, to_date('2016-2-15 10:00 am','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> select * from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
5 Elizabeth Baker 104 2 15-FEB-2016 10:00 AM 55
SQL> drop table appointment;
Table dropped.
SQL> create table appointment(
2 APPT_ID int primary key,
3 COUNSELOR varchar(20),
4 CLIENT_ID int,
5 ROOM number(1,0),
6 START_TIME date,
7 DURATION number(3,0),
8 constraint app_client_FK Foreign Key (CLIENT_ID) references client
9 );
Table created.
SQL> drop table appointment;
Table dropped.
SQL> create table appointment(
2 APPT_ID int primary key,
3 COUNSELOR varchar(20),
4 CLIENT_ID int,
5 ROOM number(1,0),
6 START_TIME date,
7 DURATION number(3,0) ,
8 Foreign Key (CLIENT_ID) references client
9 );
Table created.
SQL> insert into appointment values(1,'Quinn Fowler',100,1,to_date('2016-2-15 1:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(2,'Quinn Fowler',101,1,to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(3,'Quinn Fowler',102,1, to_date('2016-2-15 3:00 pm','yyyy-mm-dd hh:mi am'),55);
1 row created.
SQL> insert into appointment values(4,'Elizabeth Baker',105,2, to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),115);
1 row created.
SQL> select * from client;
CLIENT_ID FIRST_NAME LAST_NAME PHONE
---------- -------------------- -------------------- --------------
100 Ashley Lloyd (508) 759-1317
101 Sandra Welch (508) 786-0586
102 Clive Sanford (508) 297-2070
103 Abby Harris
104 Gene Mann (508) 478-4373
105 Jennifer Marin (508) 867-5309
6 rows selected.
SQL> select * from appointment;
APPT_ID COUNSELOR CLIENT_ID ROOM START_TIME DURATION
---------- -------------------- ---------- ---------- -------------------- ----------
1 Quinn Fowler 100 1 15-FEB-2016 01:00 PM 55
2 Quinn Fowler 101 1 15-FEB-2016 02:00 PM 55
3 Quinn Fowler 102 1 15-FEB-2016 03:00 PM 55
4 Elizabeth Baker 105 2 15-FEB-2016 02:00 PM 115
SQL> drop table client;
drop table client
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> delete from client where CLIENT_ID=104;
1 row deleted.
SQL>
SQL> delete from client where CLIENT_ID=105;
delete from client where CLIENT_ID=105
*
ERROR at line 1:
ORA-02292: integrity constraint (GOVE.SYS_C007203) violated - child record found
SQL> exit