Video
SQL Output from Video
SQL> /**************************************************************************************************************************
SQL>
SQL> Enforcing the "Not Null" 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 be managing client data.
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> 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
SQL> insert into client values(105,'Jennifer',NULL,'(508) 867-5309');
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 (508) 867-5309
6 rows selected.
SQL> drop table client;
Table dropped.
SQL> create table client(
2 CLIENT_ID int primary key,
3 FIRST_NAME varchar(20) NOT NULL,
4 LAST_NAME varchar(20) NOT NULL,
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> 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
SQL> insert into client values(105,'Jennifer',NULL,'(508) 867-5309');
insert into client values(105,'Jennifer',NULL,'(508) 867-5309')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("GOVE"."CLIENT"."LAST_NAME")
SQL> insert into client values(105,'Jennifer','Marin','(508) 867-5309');
1 row created.
SQL> update client set last_name = NULL where client_id = 105;
update client set last_name = NULL where client_id = 105
*
ERROR at line 1:
ORA-01407: cannot update ("GOVE"."CLIENT"."LAST_NAME") to NULL
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> exit