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.