Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL> `
SQL> Enforcing the Primary 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 have a list of counselors, each with a COUNSELOR_ID. We will use the COUNSELOR _ID as a unique
SQL>
SQL> identifier for each counselor so we need to be sure that no two counselors have the same value for COUNSELOR _ID. We will
SQL>
SQL> also keep track of the certifications and degrees that each counselor has earned.
SQL>
SQL>
SQL> **************************************************************************************************************************/
SQL>
SQL> create table counselor(
2 COUNSELOR_ID int,
3 FIRST_NAME varchar(20),
4 LAST_NAME varchar(20),
5 PHONE varchar(14)
6 );
Table created.
SQL> insert into counselor values(10,'Quinn','Fowler','(508) 244-1416');
1 row created.
SQL> insert into counselor values(11,'Elizabeth','Baker','(508) 243-1789');
1 row created.
SQL> insert into counselor values(12,'Darrold','Cooper',NULL);
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
SQL> insert into counselor values(NULL,'Leslie','Wright','(508) 244-5586');
1 row created.
SQL> insert into counselor values(12,'Abigail','Smith','(508) 244-6931');
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
Leslie Wright (508) 244-5586
12 Abigail Smith (508) 244-6931
SQL> drop table counselor;
Table dropped.
SQL> create table counselor(
2 COUNSELOR_ID int primary key,
3 FIRST_NAME varchar(20),
4 LAST_NAME varchar(20),
5 PHONE varchar(14)
6 );
Table created.
SQL> insert into counselor values(10,'Quinn','Fowler','(508) 244-1416');
1 row created.
SQL> insert into counselor values(11,'Elizabeth','Baker','(508) 243-1789');
1 row created.
SQL> insert into counselor values(12,'Darrold','Cooper',NULL);
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
SQL> insert into counselor values(NULL,'Leslie','Wright','(508) 244-5586');
insert into counselor values(NULL,'Leslie','Wright','(508) 244-5586')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("GOVE"."COUNSELOR"."COUNSELOR_ID")
SQL> insert into counselor values(12,'Abigail','Smith','(508) 244-6931');
insert into counselor values(12,'Abigail','Smith','(508) 244-6931')
*
ERROR at line 1:
ORA-00001: unique constraint (GOVE.SYS_C007163) violated
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
SQL> insert into counselor values(13,'Leslie','Wright','(508) 244-5586');
1 row created.
SQL> insert into counselor values(14,'Abigail','Smith','(508) 244-6931');
1 row created.
SQL> select * from counselor;
COUNSELOR_ID FIRST_NAME LAST_NAME PHONE
------------ -------------------- -------------------- --------------
10 Quinn Fowler (508) 244-1416
11 Elizabeth Baker (508) 243-1789
12 Darrold Cooper
13 Leslie Wright (508) 244-5586
14 Abigail Smith (508) 244-6931
SQL> create table certification(
2 COUNSELOR_ID int,
3 CERT_NO int,
4 CERT_ABBR varchar(30),
5 CERT_YEAR number(4,0),
6 constraint certification_PK primary key (COUNSELOR_ID, CERT_NO)
7 );
Table created.
SQL> insert into certification values (10,1,'BA',1992);
1 row created.
SQL> insert into certification values (10,2,'MC',1999);
1 row created.
SQL> select * from certification;
COUNSELOR_ID CERT_NO CERT_ABBR CERT_YEAR
------------ ---------- ------------------------------ ----------
10 1 BA 1992
10 2 MC 1999
SQL> insert into certification values (10,2,'ATR',2007);
insert into certification values (10,2,'ATR',2007)
*
ERROR at line 1:
ORA-00001: unique constraint (GOVE.CERTIFICATION_PK) violated
SQL> insert into certification values (NULL,3,'ATR',2007);
insert into certification values (NULL,3,'ATR',2007)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("GOVE"."CERTIFICATION"."COUNSELOR_ID")
SQL> insert into certification values (10,NULL,'ATR',2007);
insert into certification values (10,NULL,'ATR',2007)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("GOVE"."CERTIFICATION"."CERT_NO")
SQL> select * from certification;
COUNSELOR_ID CERT_NO CERT_ABBR CERT_YEAR
------------ ---------- ------------------------------ ----------
10 1 BA 1992
10 2 MC 1999
SQL> insert into certification values (10,3,'ATR',2007);
1 row created.
SQL> insert into certification values (12,1,'BA',2010);
1 row created.
SQL> insert into certification values (12,2,'BCSM',2014);
1 row created.
SQL> select * from certification;
COUNSELOR_ID CERT_NO CERT_ABBR CERT_YEAR
------------ ---------- ------------------------------ ----------
10 1 BA 1992
10 2 MC 1999
10 3 ATR 2007
12 1 BA 2010
12 2 BCSM 2014
SQL> drop table certification;
Table dropped.
SQL> create table certification(
2 COUNSELOR_ID int,
3 CERT_NO int,
4 CERT_ABBR varchar(30),
5 DATE_EARNED Date,
6 Primary Key (COUNSELOR_ID, CERT_NO)
7 );
Table created.
SQL> exit