ITCS 3160 - 2/25/25 2.11 and 2.12

2.11 Referential Integrity

Referential integrity rule

• A simple or composite foreign key value is fully NULL if all columns are NULL.

• Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value.

• Occasionally, data entry errors or incomplete data result in referential integrity violations

• Partially NULL foreign keys

  • A composite foreign key value is partially NULL if some, but not all, columns are NULL.

  • A partially NULL foreign key never matches a primary key, since primary key columns cannot be NULL.

  • Partially NULL foreign keys are ambiguous and generally considered a violation of referential integrity.

Referential integrity violations

• Referential integrity can be violated in four ways:

  1. A primary key is updated.

  2. A foreign key is updated.

  3. A row containing a primary key is deleted.

  4. A row containing a foreign key is inserted.

• Only these four operations can violate referential integrity. Primary key inserts and foreign key deletes never violate referential integrity.

Referential integrity actions

• An insert, update, or delete that violates referential integrity can be corrected manuall

• RESTRICT rejects an insert, update, or delete that violates referential integrity.

• SET NULL sets invalid foreign keys to NULL.

• SET DEFAULT sets invalid foreign keys to the foreign key default value.

• CASCADE propagates primary key changes to foreign keys.

• CASCADE behaves differently for primary key updates and deletes. If a primary key is deleted, rows containing matching foreign keys are deleted. If a primary key is updated, matching foreign keys are updated to the same value

ON UPDATE and ON DELETE clauses

• Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint.

  • ON UPDATE and ON DELETE are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE.

• ON UPDATE and ON DELETE determine what happens to the foreign key when the referenced primary key is updated or deleted.

  • When several foreign keys refer to the same primary key, different actions can be specified for each foreign key

• MySQL has several limitations on primary key updates and deletes:

  • RESTRICT is applied when the ON UPDATE or ON DELETE clause is omitted.

  • SET NULL cannot be used when a foreign key is not allowed NULL values.

  • SET DEFAULT is not supported in some MySQL configurations.

2.12 Constraints

Column and table constraints

• A constraint is a rule that governs allowable values in a database

  • are based on relational and business rules, and implemented with special keywords in a CREATE TABLE statement

  • The database automatically rejects insert, update, and delete statements that violate a constraint

• column constraint appears after the column name and data type in a CREATE TABLE statement

  • Column constraints govern values in a single column.

  • Ex: NOT NULL is a column constraint

• table constraint appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns

  • Ex: FOREIGN KEY is a table constraint

• NOT NULL - Column constraint only

  • The NOT NULL constraint governs values in a single column and must appear in a column declaration.

• DEFAULT

• PRIMARY KEY - Either column or table constraint

  • A simple PRIMARY KEY constraint governs values in a single column and may appear either in a column declaration or a separate clause.

  • A composite PRIMARY KEY constraint governs values in multiple columns and must appear in a separate clause.

• FOREIGN KEY - Table constraint only

  • A FOREIGN KEY constraint governs values in both the foreign key column(s) and the referenced primary key column(s).

  • The foreign and primary keys may be either simple or composite.

  • The FOREIGN KEY constraint must appear in a separate clause.

UNIQUE constraint

• UNIQUE constraint ensures that values in a column, or group of columns, are unique.

  • When applied to a single column, UNIQUE may appear either in the column declaration or a separate clause.

  • When applied to a group of columns, UNIQUE is a table constraint and must appear in a separate clause

• The UNIQUE constraint can be applied to primary key columns but is unnecessary, since primary keys are automatically unique

CHECK constraint

• CHECK constraint specifies an expression on one or more columns of a table.

  • The constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL.

Constraint names

• Table constraints may be named using the optional CONSTRAINT keyword, followed by the constraint name and declaration.

• If no name is provided, the database generates a default name.

• Constraint names appear in error messages when constraints are violated.