Integrity Constraints

Lecture 5 · integrity constraints guard against accidental damage to the database, by ensuring that authorized changes do not result in a loss of data consistency.

Examples of rules a constraint expresses

A checking account must have a balance greater than $10,000.00
The salary of a bank employee must be at least $4.00 an hour
A customer must have a (non-null) phone number

Two types of integrity constraint:

Integrity constraints on a single relation — not null, primary key, unique, check (P).
Referential integrity — a value appearing in one relation must also appear in another (foreign keys).

1 · Constraints on a single relation

`not null`

not null

name    varchar(20)   not null
budget  numeric(12,2) not null

`unique (A1, A2, …, Am)`

The unique specification states that the attributes A₁, A₂, … Aₘ form a candidate key.

Candidate keys are permitted to be null — in contrast to primary keys, which are automatically not null. This is exactly why the “one Active salary per employee” trick (a generated column that is NULL for inactive rows) works.

`check (P)` — where P is a predicate

check clause

create table section (
    course_id     varchar(8),
    sec_id        varchar(8),
    semester      varchar(6),
    year          numeric(4,0),
    building      varchar(15),
    room_number   varchar(7),
    time_slot_id  varchar(4),
    primary key (course_id, sec_id, semester, year),
    check (semester in ('Fall','Winter','Spring','Summer')
        or semester in ('F','W','Sp','S'))
);

Complex check clauses

check (time_slot_id in (select time_slot_id from time_slot)) — why not use a foreign key here instead?
“Every section has at least one instructor teaching the section.” — how would you write that?

Unfortunately: a subquery inside a check clause is not supported by pretty much any database. The alternative is a trigger. create assertion <name> check <predicate>; is also not supported by anyone. Triggers →

2 · Referential integrity

Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Formally: let A be a set of attributes, and R and S two relations containing A, where A is the primary key of S. Then A is a foreign key of R if for any value of A appearing in R, that value also appears in S.

instructor

ID	name	dept_name	salary
1	Abdur	CSE	10000
2	aman	CSE	12500
3	bilal	EEE	17000
4	morshed	EEE	12000
5	rashid	BBA	10000
6	irshad	BBA	7800
7	Irfan	Biology	15400
8	Mobassir	Biology	20100

department

ID	dept_name	dept_full_name	building
1	CSE	Computer Science and Engineering	B101
2	EEE	Electrical and Electronic Engineering	B200
3	BBA	Bachelor of Business Administration	B201

The violation: instructors 7 and 8 have dept_name = 'Biology', but there is no Biology tuple in department. Referential integrity is broken — either insert (4, Biology, Department of Biology, B300) into department, or reject those two instructor rows.

declaring the foreign key

create table instructor (
    ID         char(5),
    name       varchar(20) not null,
    dept_name  varchar(20),
    salary     numeric(8,2),
    primary key (ID),
    foreign key (dept_name) references department
);

Cascading actions in referential integrity

Cascading actions are rules on a foreign key that tell the DB what to do in the child table when the referenced parent row is updated or deleted, so that referential integrity stays valid.

cascading

create table instructor (
    ID         char(5),
    name       varchar(20) not null,
    dept_name  varchar(20),
    salary     numeric(8,2),
    primary key (ID),
    foreign key (dept_name) references department
        on delete cascade
        on update cascade
);

Delete or update of the shared attribute in the department table will now automatically delete or update the value in the instructor table. Alternative actions to cascade: set null, set default.

Action	Effect on the child	Typical use
`ON DELETE CASCADE`	Delete the parent row → the child rows are deleted too.	Attendance rows of a deleted employee.
`ON UPDATE CASCADE`	Change the parent key → the child FK values follow automatically.	Renaming a department code.
`SET NULL`	The child FK becomes NULL (the column must be nullable).	An approver who leaves the university.
`SET DEFAULT`	The child FK reverts to its declared DEFAULT value.	Moving orphans to a “General” department.
`RESTRICT / NO ACTION`	Refuse the delete/update while children still reference the row.	A department that still has instructors.

Summary of the four classic integrity types:Domain (data type / check) · Entity (primary key = unique + not null) · Referential (foreign key) · User-defined (unique, business checks). Enforcing them in the DBMS rather than in application code means no application can ever bypass them.

Applied in the project: Task 2 — MySQL schema →