Stuart Banner - M358 - Integrity Constraints

Integrity Constraints

Data integrity is violated if there is any difference between the data and the real world it represents (correctness) or the between the data in differents parts of the database (consistency). Integrity constraints are included as part of a table definition.

Kinds of Constraint

NOT NULL CONSTRAINT - each entry in a constrained column is NOT NULL
UNIQUE CONSTRAINT - each (non-null) value in a column(s) is distinct. Includes primary and alternate key constraints.
REFERENTIAL CONSTRAINT - each (non-null) value in a column(s) has a single matching value in the table to which it refers.
CHECK CONSTRAINT - requires that a given search condition is not false for any row of a table.

Column constraints can be written as part of a column definition as per NOT NULL. Or it can be given as a table constraint. All constraints can be defined in the CREATE TABLE statement or added as below:

ALTER TABLE ADD <table constraint>

Primary Key Constraints

The most common unique constraint. A table has just one primary key constraint.

CREATE TABLE small_country 
(name CHAR (16) PRIMARY KEY,
Gdp DECIMAL (4, 1),
Cars INTEGER,
Population DECIMAL (6, 1))

Where the primary key includes more than one column:

CREATE TABLE enrolment 
(student_id CHAR (3),
course_code CHAR (2),
tutor_number CHAR (4),
PRIMARY KEY (student_id, course-code))

Unique Constraints

Otherwise known as alternate keys. SQL expresses these as follows:

Column constraint:

title CHAR (10) UNIQUE

Table constraint:

UNIQUE (title)

Adding to an existing table:

ALTER TABLE mod_course
   ADD UNIQUE (title)

Referential Constraints

Part of the foreign key mechanism, where the foreign key is a column(s) matching those of the primary key of another table. Such matching values represent a relationship.

Column constraint:

counsellor_no CHAR (4) NOT NULL REFERENCES staff

Table constraint:

FOREIGN KEY (counsellor_no) REFERENCES staff

Adding to an existing table:

ALTER TABLE mod_student
   ADD FOREIGN KEY (counsellor_no) REFERENCES staff

A referential constraint prevents a foreign key having a value that does not exist as a primary key of the referenced table.

In certain circumstances the use of referential constraints can prevent certain actions within the DBMS e.g. deleting/updating rows. To combat this it is necessary to define referential actions. these are invoked when certain conditions arise:

SET NULL - sets the value of the foreign key to null.
SET DEFAULT - sets the value of the foreign kEy to its default value.
CASCADE - deletes the dependent row.

ALTER TABLE mod_student
   ADD FOREIGN KEY (counsellor_no) REFERENCES staff
   ON DELETE SET DEFAULT

The CASCADE statement deletes associated rows:

ALTER TABLE mod_enrolment
   ADD FOREIGN KEY (student_id) REFERENCES mod_student
   ON DELETE SET CASCADE

Check Constraints

Used for search conditions that must not be false, as opposed to must be true. This allows for NULLS in the table.

Column constraint:

registered SMALLINT
   CHECK (registered BETWEEN 1988 AND 2010)

Table constraint:

CHECK (region =
   (SELECT region
    FROM staff
    WHERE counsellor_no = staff_no))

satisfied by each corresponding row of each table having the same values from the correlated subquery.

Domain Constraints

Not a constraint but a way in which there is a representation of constraints.

CREATE DOMAIN credit_points AS SMALLINT
NOT NULL DEFAULT 60
CHECK (VALUE IN (30, 60))

Using a check constraint in a domain definition introduces the variable VALUE.

Move on to Views.

Comments, suggestions, ideas to
Stuart Banner