Table Constraints
David Berry
http://buildingbettersoftware.blogspot.com/
Table Constraints Overview
Table constraints
Define rules your data must follow
Examples
Every student must have a unique student id number
Every course must be associated with a department
Module Outline
Primary Keys
Foreign Keys
Check Constraints
Primary Keys
Uniquely identifies each row in a table
Can be defined over one or more
columns
Cannot contain a NULL value in any
primary key column
Primary Key Types
Natural Key Surrogate Key
Combination of naturally
A unique value is generated
occurring columns that form
for each row
a unique key
The real reason I favor surrogate keys….
I am a terrible typist!
Primary Key Definition
CREATE TABLE departments
(
department_code VARCHAR2(2) NOT NULL PRIMARY KEY,
department_name VARCHAR2(64) NOT NULL
);
Primary Key Using Constraint Clause
CREATE TABLE courses
(
department_code VARCHAR2(2) NOT NULL,
course_number NUMBER(3,0) NOT NULL,
course_title VARCHAR2(64) NOT NULL,
course_description VARCHAR2(512) NOT NULL,
credits NUMBER(3,1) NOT NULL,
CONSTRAINT pk_courses
PRIMARY KEY (department_code, course_number)
);
Add a Primary Key to an Existing Table
ALTER TABLE courses
CONSTRAINT pk_courses
ADD PRIMARY KEY (department_code, course_number);
Primary Key Recommendations
Make sure every table Values of a primary
has a primary key key should be
defined immutable
Oracle ROWID
Represents the address of the row in the table
SQL> SELECT rowid, zip_code, city, state
2 FROM zip_codes;
ROWID ZIP_C CITY ST
------------------ ----- ------------------------------ --
AAAXoNAAGAAAJzeAAA 11201 Brooklyn NY
AAAXoNAAGAAAJzeAAB 75201 Dallas TX
AAAXoNAAGAAAJzeAAC 80401 Golden CO
AAAXoNAAGAAAJzeAAD 92101 San Diego CA
Foreign Keys
Put the relational in relational databases
What Is a Foreign Key
For a record to exist in the COURSES table, it must contain a valid
DEPARTMENT_CODE value from the DEPARTMENTS table
Defining a Foreign Key
CREATE TABLE courses
(
department_code VARCHAR2(2) NOT NULL,
course_number NUMBER(3,0) NOT NULL,
course_title VARCHAR2(64) NOT NULL,
course_description VARCHAR2(512) NOT NULL,
credits NUMBER(3,1) NOT NULL,
CONSTRAINT pk_courses
PRIMARY KEY (department_code, course_number),
CONSTRAINT fk_courses_department_code
FOREIGN KEY (department_code)
REFERENCES departments (department_code),
);
Add a Foreign Key to an Existing Table
ALTER TABLE courses
CONSTRAINT fk_courses_department_code
ADD FOREIGN KEY (department_code)
REFERENCES departments (department_code);
On Delete Cascade
CREATE TABLE courses
(
department_code VARCHAR2(2) NOT NULL,
course_number NUMBER(3,0) NOT NULL,
course_title VARCHAR2(64) NOT NULL,
course_description VARCHAR2(512) NOT NULL,
credits NUMBER(3,1) NOT NULL,
CONSTRAINT pk_courses
PRIMARY KEY (department_code, course_number),
CONSTRAINT fk_courses_department_code
FOREIGN KEY (department_code)
REFERENCES departments (department_code)
ON DELETE CASCADE
);
On Delete Cascade
departments
department_code name
MA Math
PH Physics
CS Computer Science
courses
department_code course_number Title
MA 101 Calculus 1
DELETE FROM departments MA 102 Calculus 2
WHERE department_code = ‘PH’;
PH 101 Physics 1
PH 102 Physics 2
CS 101 Intro to
Programming
On Delete Cascade
Without on delete cascade
DELETE FROM courses WHERE department_code = ‘PH’;
DELETE FROM departments WHERE department_code = ‘PH’;
COMMIT;
To delete a primary key value, no child records can exist
Deferred Constraints
CREATE TABLE courses
(
department_code VARCHAR2(2) NOT NULL,
course_number NUMBER(3,0) NOT NULL,
course_title VARCHAR2(64) NOT NULL,
course_description VARCHAR2(512) NOT NULL,
credits NUMBER(3,1) NOT NULL,
CONSTRAINT pk_courses
PRIMARY KEY (department_code, course_number),
CONSTRAINT fk_courses_department_code
FOREIGN KEY (department_code)
REFERENCES departments (department_code)
DEFERRABLE
INITIALLY IMMEDIATE
-- INITIALLY DEFERRABLE
);
Using a Deferred Constraint
departments set constraint
fk_courses_depatment_code deferred;
department_code name
MA Math
UPDATE departments
SET department_code = ‘CO’
PH Physics WHERE department_code = ‘CS’;
CS Computer Science
UPDATE courses
SET department_code = ‘CO’
WHERE department_code = ‘CS’;
COMMIT;
courses
department_code course_number Title
MA 101 Calculus 1
MA 102 Calculus 2
PH 101 Physics 1
PH 102 Physics 2
CS 101 Intro to
Programming
Foreign Key Options Summary
Why are you having to cascade deletes or defer constraints?
One time cleanup or typical business operation
Using on delete cascade
Is deleting data really the correct decision?
Can the parent record be marked ‘inactive’
Using deferred constraints
Consider a surrogate key for a primary key
Check Constraints
Validate the format of
Check that a value is Compare two values
a value using a
within a given range in different columns
regular expression
Check If Value is Within a Range
CREATE TABLE courses
(
department_code VARCHAR2(2) NOT NULL,
course_number NUMBER(3,0) NOT NULL,
course_title VARCHAR2(64) NOT NULL,
course_description VARCHAR2(512) NOT NULL,
credits NUMBER(3,1) NOT NULL,
CONSTRAINT pk_courses
PRIMARY KEY (department_code, course_number),
CONSTRAINT fk_courses_department_code
FOREIGN KEY (department_code)
REFERENCES departments (department_code),
CONSTRAINT ck_courses_course_number
CHECK (course_number BETWEEN 100 AND 999)
);
Check If Value is Within a Domain
CREATE TABLE states
(
state_code VARCHAR2(2) NOT NULL,
state_name VARCHAR2(30) NOT NULL,
region VARCHAR2(2) NOT NULL,
CONSTRAINT pk_states
PRIMARY KEY (state_code),
CONSTRAINT ck_state_regions
CHECK (region IN (‘NE’, ‘SE’, ‘MW’, ‘SC’, ‘NW’, ‘SW’))
);
Emulate a Boolean Column
CREATE TABLE students
(
student_id NUMBER(6) NOT NULL,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
email_address VARCHAR2(128) NOT NULL,
likes_ice_cream NUMBER(1) NULL,
CONSTRAINT pk_students PRIMARY KEY (student_id),
CONSTRAINT ck_studens_ice_cream
CHECK (likes_ice_cream IN (0,1))
);
Compare Two Values
CREATE TABLE Orders
(
order_id NUMBER(9) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_date DATE NOT NULL,
ship_date DATE NULL,
order_status VARCHAR2(1) NOT NULL,
CONSTRAINT pk_orders
PRIMARY KEY (order_id),
CONSTRAINT ck_orders_order_ship_date
CHECK (ship_date > order_date)
);
Validate Format Using a Regular Expression
CREATE TABLE zip_codes
(
zip_code VARCHAR2(5) NOT NULL,
city VARCHAR2(30) NOT NULL,
state_code VARCHAR2(30) NOT NULL,
CONSTRAINT pk_codes
PRIMARY KEY (state_code),
CONSTRAINT ck_zip_code_format
CHECK (REGEXP_LIKE (zip_code, '^[0-9]{5}$') )
);
Add a Constraint to an Existing Table
ALTER TABLE zip_codes
ADD CONSTRAINT ck_zip_codes_format
CHECK (REGEXP_LIKE (zip_code, '^[0-9]{5}$') );
Disabling and Enabling Constraints
Disabling a constraint
ALTER TABLE courses
DISABLE CONSTRAINT fk_courses_department_code;
Enabling a constraint
ALTER TABLE courses
ENABLE CONSTRAINT fk_courses_department_code;
Constraints and Data Integrity
Multiple options are
Data integrity is critical
available for enforcing
to your business data
data integrity
Why Use Database Constraints
• Required for many business functions
Clean Data • Can reduce the amount of coding
needed to deal with “bad data”
Consistent
• All applications must comply with
Enforcement of
database constraints
Constraints
Enforcing Constraints in Your Application
App is • Faster feedback on invalid
closer to data
user • Less frustrating to user
General • Mitigate security concerns
good • Reduce crashes due to
practice invalid data
Constraints Strategy
Database Constraints
• One set of tools in an overall strategy
• Make use of all tools available
Validate both in Database and Applications
• Applications: provide immediate user feedback
• Database: consistently enforce rules across all applications
Performance Impacts
• Constraints can be checked in milliseconds
• Bad data take s along time to clean up
Summary
Check
Primary Keys Foreign Keys
Constraints
