1. No category

Database Programming
Sections 10 – Constraints
CONSTRAINT TYPES





NOT NULL Constraints
UNIQUE Constraints
PRIMARY KEY Constraints
FOREIGN KEY Constraints
CHECK Constraints
Marge Hohly
2
Defining CONSTRAINTS
 What are constraints?
 Database rules
 Constraints always have a name
 Given by you/DBA when constraint is
created (preferred method because
names are meaningful)
 Given by the system when constraint is
created (names are not meaningful)
Marge Hohly
3
Defining CONSTRAINTS
 Two Ways to Define Constraints
during Table Creation
 Table-Level
 NOT NULL cannot be defined at the tablelevel
 If the word CONSTRAINT is used in the
CREATE TABLE statement, the constraint
must be given a name
 Composite-key constraints must be defined
at the table-level
 Column-Level
 NOT NULL must be defined at the ColumnLevel
Marge Hohly
4
Naming Constraints
 Every constraint has a name.
 User defined constraint name, ex.
clients_client_num_pk
 System named constraint, ex. SYS_C0xxxx
 Format table-name_columnname_constraint-type
 Limit of 30 characters
 If defined at table creation must be named.
Marge Hohly
5
Column Level Constraint
 Refers to a single column
 CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT
clients_client_num_pk PRIMARY KEY,
first_name VARCHAR2(14) NOT NULL,
last_name
VARCHAR2(13));
 Primary key defined at the column level
 SYS_Cn (where n is a unique integer) NOT
NULL constraint
Marge Hohly
6
Composite key constraint
 Composite defined at Table level
 CREATE TABLE clients (
client_number NUMBER(4) NOT NULL,
first_name VARCHAR2(20),
last_name
VARCHAR2(20),
phone
VARCHAR2(20),
email
VARCHAR2(10) NOT NULL,
CONSTRAINT clients_phone_email_uk UNIQUE
(email,phone));
Marge Hohly
7
Defining CONSTRAINTS

Table-Level Constraints – at the bottom
Note: The words
EXAMPLE:
“Foreign Key” are
CREATE TABLE copy_employees(
Used at the table
level
employee_id NUMBER(6),
first_name VARCHAR2(20),
job_id VARCHAR2(10),
CONSTRAINT cemp_emp_id_pk PRIMARY
KEY(employee_id),
CONSTRAINT cemp_job_id_fk FOREIGN KEY(job_id)
REFERENCES jobs(job_id),
CONSTRAINT cemp_first_name_uk UNIQUE
(first_name),
CONSTRAINT cemp_emp_id_ck CHECK
(employee_id<=999999));
Marge Hohly
8
Marge Hohly
9
NAMING at TABLE LEVEL
 Constraint naming format table_col_type


CONSTRAINT constraint_name TYPE OF
CONSTRAINT(column_name)

CONSTRAINT cemp_emp_id_pk PRIMARY KEY(employee_id)

CONSTRAINT cemp_emp_id_lname_pk PRIMARY KEY(employee_id,last_name)
CONSTRAINT constraint_name TYPE OF CONSTRAINT(column_name)
REFERENCES othertablename(column_name)

CONSTRAINT cemp_job_id_fk FOREIGN KEY(job_id)
REFERENCES copy_jobs(job_id),
Marge Hohly
10
NAMING at COLUMN LEVEL
Column Level Assigning A Constraint Name:
System Named:
 column_name datatype() TYPE OF CONSTRAINT
 employee_id NUMBER(6) PRIMARY KEY
 User Named:
 column_name datatype() CONSTRAINT constraint
name TYPE OF CONSTRAINT
 employee_id NUMBER(6) CONSTRAINT
c2emp_emp_id_pk PRIMARY KEY
 Foreign Key:
 column_name datatype() CONSTRAINT
constraint_name REFERENCES
othertablename(column_name)


Marge Hohly
11
Defining CONSTRAINTS
 Column-Level Constraints

Example:
CREATE TABLE copy2_employees(
employee_id NUMBER(6) CONSTRAINT
c2emp_emp_id_pk PRIMARY KEY,
CONSTRAINT c2emp_emp_id_ck
CHECK(employee_id<=999999),
first_name VARCHAR2(20)
CONSTRAINT c2emp_first_name_nn NOT NULL,
last_name VARCHAR2(20)
CONSTRAINT c2emp_last_name_nn NOT NULL,
address VARCHAR2(20) CONSTRAINT
c2emp_address_ck NOT NULL,
job_id VARCHAR2(10)
CONSTRAINT c2emp_job_id_fk REFERENCES
copy_jobs(job_id));
Marge Hohly
12
NOT NULL Constraint
 Requires that every row has a value
for the NOT NULL column
 Named with _nn suffix
Marge Hohly
13
UNIQUE constraint
 No two rows have the same value
 Every value in the column or set of
columns (a composite key) are
unique
 Names with _uk as a suffix
 If a composite unique key must be
defined at Table level
 Column level unique constraint is
defined a column level
Marge Hohly
14
Primary Key Constraints
 Primary key constraint is a column or
set of columns that is uniquely
identifies each row in a table
 Must satisfy both conditions:
 No column that is part of the primary
key can contain a null value.
 A table can have only one primary key.
 Named with _pk suffix
Marge Hohly
15
Primary Key Constraints
 CREATE TABLE clients
(client_number NUMBER(4)
CONSTRAINT clients_client_num_pk
PRIMARY KEY,
first_name VARCHAR2(14),
last_name VARCHAR2(13));
Marge Hohly
16
Foreign Key(Referential
Integrity) constraints
 CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT
clients_client_num_pk PRIMARY KEY,
first_name VARCHAR2(14),
last_name VARCHAR2(13),
department_id VARCHAR2(4,0),
CONSTRAINT clients_dept_id_fk FOREIGH
KEY(department_id) REFERENCES
departments(department_id));
 Names _fk suffix
Marge Hohly
17
Foreign Key syntax
 Column-level
 song_id NUMBER(5) CONSTRAINT
d_track_list_song_id_fk REFERENCES
d_songs(id)
 Table-level
 CONSTRAINT d_track_list_song_id_fk
FOREIGN KEY(song_id) REFERENCES
d_songs(id)
Marge Hohly
18
ON DELETE CASCADE option
 Used when defining the foreign key
enables the dependent rows in the child
table to be deleted when a row in the
parent table is deleted.
 Without this you can not delete the
parent record if there are any rows in
the child table with the key value.
 Use either on definition of FK
ON DELETE CASCADE or
ON DELETE SET NULL
Marge Hohly
19
ON DELETE CASCADE option
 Column-level
 song_id NUMBER(5) CONSTRAINT
d_track_list_song_id_fk REFERENCES
d_songs(id) ON DELETE CASCADE
 Table-level
 CONSTRAINT d_track_list_song_id_fk
FOREIGN KEY(song_id) REFERENCES
d_songs(id) ON DELETE CASCADE
 Could use ON DELETE SET NULL also
Marge Hohly
20
CHECK constraints
 Explicitly defines a condition that
must be met.
 Condition must be either TRUE or
unknown(due to a null)
 May refer to values in any column in
the table, but not columns in other
tables
Marge Hohly
21
CHECK constraint
Marge Hohly
22
Marge Hohly
23
Marge Hohly
24
Adding Constraints AFTER Table is created:
 First, create a table that does not already
have constraints:
 CREATE TABLE copy3_employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
department_id NUMBER(4));
Marge Hohly
25
Alter statement requires:
Name of the table
Name of the constraint
Type of constraint
Name of the column affected by the
constraint
 Example:
ALTER TABLE copy1_d_clients
ADD CONSTRAINT clients_client_num_pk
PRIMARY KEY(client_number);




Marge Hohly
26
Adding Constraints AFTER Table is created:
 Secondly, add the constraints:
ALTER TABLE copy3_employees
ADD CONSTRAINT emp3_emp_id_pk
PRIMARY KEY(employee_id);
ALTER TABLE copy3_employees
ADD CONSTRAINT emp3_emp_id_fk
FOREIGN KEY(department_id)
REFERENCES
copy_departments(department_id);
Marge Hohly
27
Adding Constraints AFTER Table is created:
 NOTE!!! For NOT NULL constraints, use
the MODIFY keyword in the ALTER
TABLE statement instead of ADD
 ALTER TABLE copy3_employees
MODIFY (first_name CONSTRAINT
emp3_first_name_nn NOT NULL);
 NOT NULL constraints can only be added
if the column does not already contain
null values
Marge Hohly
28
Miscellaneous Constraint Information ...
 If the word CONSTRAINT is used in a
CREATE TABLE statement, the constraint
must be given a name
 Constraints that contain more than one
column are called composite key
constraints and must be specified at the
table level by placing a comma between
the column names
 There is no limit to the number of CHECK
CONSTRAINTS that can be specified for a
column
Marge Hohly
29
Miscellaneous FK Constraints Information...
 Another name for FOREIGN KEY
CONSTRAINTS is REFERENCIAL INTEGRITY
CONSTRAINTS
 When specifying FOREIGN KEY
CONSTRAINTS, the table that contains the
PRIMARY KEY is called the PARENT TABLE.
The table that contains the FOREIGN KEY
CONSTRAINT is called the CHILD TABLE.
Marge Hohly
30
DISABLING CONSTRAINTS
 Constraints can be disabled
 Examples:
 ALTER TABLE copy3_employees
DISABLE CONSTRAINT
emp3_emp_id_pk;
 ALTER TABLE copy3_employees
DISABLE CONSTRAINT
emp3_emp_id_pk CASCADE;

This will cause any FOREIGN KEY that references this primary
key to also be disabled.
Marge Hohly
31
ENABLING CONSTRAINTS
 EXAMPLES:
 ALTER TABLE copy3_employees
ENABLE CONSTRAINT emp3_emp_id_pk
 Note: This does not enable the foreign key in the child
tables
Marge Hohly
32
DROPPING CONSTRAINTS
 Examples:

ALTER TABLE table_name
DROP CONSTRAINT TYPE (column_name)[CASCADE];

ALTER TABLE table_name
DROP CONSTRAINT name[CASCADE];

ALTER TABLE c_clients
DROP PRIMARY KEY CASCADE;
Marge Hohly
33
Using the DISABLE clause
 Use DISABLE clause with ALTER
TABLE or CREATE TABLE statement
 ALTER TABLE copy_d_clients
DISABLE CONSTRAINT
clients_client_num_pk
 CREATE TABLE copy_d_clients
(client_number NUMBER(5) PRIMARY
KEY DISABLE);
Marge Hohly
34
CASCADE clause
 CASCADE clause disables dependent
integrity constrains.
 If later enabled, the dependent constraints
are not automatically enabled
 ALTER TABLE table_name
DISABLE CONSTRAINT
constraint_name [CASCADE];
 ALTER TABLE d_clients
DISABLE CONSTRAINT
clients_client_num_pk CASCADE;
Marge Hohly
35
Enabling Constraints
 Use ENABLE clause in the ALTER TABLE
statement
 ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
 ALTER TABLE d_clients
ENABLE CONSTRAINTS
clients_client_num_pk;
 Can use ENABLE clause in both CREATE
TABLE and ALTER TABLE
Marge Hohly
36
Cascading Constraints
 Used along with DROP COLUMN
clause
 Drops all referential-integrity
constraints the refer to primary and
unique keys defined on the dropped
columns
 ALTER TABLE table_name
DROP(column name(s)) CASCADE
CONSTRAINTS;
Marge Hohly
37
Viewing Constraint
 Use the DESCRIBE command to confirm its existence .
 DESCRIBE can only verify is the NOT NULL constraint.
 NOT NULL constraint appears in the data dictionary as
a CHECK constraint.
 Use a query of the USER_CONSTRAINTS table to view
all constraints on your table.

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE TABLE_NAME ='table_name';

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE TABLE_NAME ='COPY3_EMPLOYEES‘;
Marge Hohly
38
QUERY THE DATA DICTIONARY
 SELECT constraint_name, constraint_type,
table_name, status
FROM user_constraints;
 Types:




P = Primary Key
R = Foreign Key (Referential)
C = Check (Includes NOT NULL)
U = Unique
Marge Hohly
39
Viewing Constraint
 SELECT constraint_name,
column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
Marge Hohly
40
Viewing Constraints
Marge Hohly
41
Constraint Rules in Review:
 The NOT NULL constraint can be specified only
at the column level, not the table level
 PRIMARY, UNIQUE, FOREIGN KEY, and CHECK
constraints can be specified at the column level
OR the table level
 Where to add constraints as part of the
CREATE TABLE statement:
 At the column level
 Following the data type
 At the table level
 Following the comma after the last column name and
data type
Marge Hohly
42
Miscellaneous FK Constraint
Information:





Another name for FOREIGN KEY CONSTRAINTS is
REFERENTIAL INTEGRITY CONSTRAINTS
When specifying FOREIGN KEY CONSTRAINTS, the table that
contains the PRIMARY KEY is called the PARENT TABLE. The
table that contains the FOREIGN KEY CONSTRAINT is called the
CHILD TABLE
When a DELETE statement is issued for a row in the PARENT
TABLE, the command will not execute if there are rows in the
CHILD TABLE that use the row from the PARENT TABLE
Add ON DELETE CASCADE to the end of a FOREIGN KEY
constraint to force CHILD TABLE rows to be deleted when
PARENT TABLE rows are deleted
Add ON DELETE SET NULL to the end of the FOREIGN KEY
constraint to force CHILD TABLE rows to be replaced with NULL
values when PARENT TABLE rows are deleted
Marge Hohly
43