2.12f

Database Constraints

  • Constraints define allowable values in a database based on relational business rules.

  • Defined using special keywords in a CREATE TABLE statement.

  • The database will automatically reject any INSERT, UPDATE, or DELETE statements that violate these constraints.

Types of Constraints

Column Constraints

  • Defined in a separate clause of a CREATE TABLE statement.

  • Govern values in a single column.

  • For example, NOT NULL is a typical column constraint.

Table Constraints

  • Also defined in a separate clause.

  • Govern values in one or more columns.

  • FOREIGN KEY is an example of a table constraint.

DEFAULT Constraint

  • Does not limit allowable values in a column but specifies what value is inserted if a column is omitted from an INSERT statement.

  • Not always considered a direct constraint.

PRIMARY KEY

  • Can be a column or table constraint.

  • The primary key uniquely identifies each record in a database table.

  • May apply to a single column or a group of columns.

  • When applied to a single column, it can appear in the column declaration or in a separate clause.

  • Similar to the UNIQUE constraint, although the PRIMARY KEY is inherently unique, making the use of UNIQUE on primary key columns unnecessary.

UNIQUE Constraint

  • A table constraint that must appear in a separate clause of the CREATE TABLE statement.

  • Can be applied to columns that are part of a primary key but is not necessary.

CHECK Constraint

  • Consists of an expression on one or more columns of a table.

  • A CHECK constraint is satisfied when the expression evaluates to TRUE or NULL and violated when FALSE.

  • It may be defined either in the column declaration or in a separate clause.

Defining Constraints

  • Constraints can be defined using the optional CONSTRAINT keyword followed by a name.

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

  • Named constraints may appear in error messages when violations occur.

  • Most column constraints cannot be named, but a CHECK column constraint can be named using the CONSTRAINT clause in the column declaration.

Altering Constraints

  • Use the ALTER TABLE statement to modify constraints in an existing table.

  • Followed by ADD, DROP, or CHANGE clauses:

    • Unnamed constraints like NOT NULL or DEFAULT can be added or dropped with a CHANGE clause.

    • Named constraints can be dropped with a DROP clause.

  • Dropping a table fails if a foreign key constraint refers to the table's primary key; the foreign key constraint or the referenced table must be dropped before the table can be dropped.