Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Enforcing Data Validation Rules with the Check 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>
SQL>
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: 11:00 am
SQL> DURATION: 25 minutes
SQL>
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> insert into appointment values(5,'Elizabeth Baker',104,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),25);
1 row created.
SQL> insert into appointment values(6,'Elizabeth Baker',103,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),125);
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 11:00 AM 25
6 Elizabeth Baker 103 3 15-FEB-2016 11:00 AM 125
6 rows selected.
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 valid_duration Check(DURATION >=30 and DURATION <=115)
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,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),25);
insert into appointment values(5,'Elizabeth Baker',104,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),25)
*
ERROR at line 1:
ORA-02290: check constraint (GOVE.VALID_DURATION) violated
SQL> insert into appointment values(6,'Elizabeth Baker',103,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),125);
insert into appointment values(6,'Elizabeth Baker',103,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),125)
*
ERROR at line 1:
ORA-02290: check constraint (GOVE.VALID_DURATION) 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> insert into appointment values(5,'Elizabeth Baker',104,3, to_date('2016-2-15 1100 am','yyyy-mm-dd hh:mi am'),30);
1 row created.
SQL> insert into appointment values(6,'Elizabeth Baker',103,3, to_date('2016-2-15 1100 am','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
5 Elizabeth Baker 104 3 15-FEB-2016 11:00 AM 30
6 Elizabeth Baker 103 3 15-FEB-2016 11:00 AM 115
6 rows selected.
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 Check (DURATION >=30 and DURATION <=115)
9 );
Table created.
SQL> select sysdate from dual;
SYSDATE
--------------------
23-JAN-2016 05:00 PM
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 Check (START_DATE >=SYSDATE)
9 );
Check (START_DATE >=SYSDATE)
*
ERROR at line 8:
ORA-00904: "START_DATE": invalid identifier
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 Check (ROOM >= 1),
9 Check (ROOM <=5 )
10 );
Table created.