Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Enforcing the Unique 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> 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> 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> 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> 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 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> APPT_ID: 5
SQL> COUNSELOR: Elizabeth Baker
SQL> CLIENT_ID: 104
SQL> ROOM: 3
SQL> Date: 15-FEB-2016
SQL> TIME: 2:00 pm
SQL> DURATION: 55 minutes
SQL>
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> insert into appointment values(5,'Elizabeth Baker',104,3, to_date('2016-2-15 2:00 pm','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 3 15-FEB-2016 02:00 PM 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 unique_counselor_start Unique (COUNSELOR, START_TIME)
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 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',104,2, to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),55);
insert into appointment values(5,'Elizabeth Baker',104,2, to_date('2016-2-15 2:00 pm','yyyy-mm-dd hh:mi am'),55)
*
ERROR at line 1:
ORA-00001: unique constraint (GOVE.UNIQUE_COUNSELOR_START) violated
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 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 Unique (COUNSELOR, START_TIME)
9 );
Table created.
SQL> exit