Constraints


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

  • are based on relational and business rules
  • implemented with special keywords in a CREATE TABLE statement
  • database auto-rejects statements that violate a constraint
  • e.g.,
    • NOT NULL
    • DEFAULT
    • PRIMARY KEY
    • FOREIGN KEY

Column and Table Constraints

A column constraint govern values in a single column.

  • appears after the column name and data type in a CREATE TABLE statement
  • e.g., NOT NULL

A table constraint governs values in one or more columns.

  • appears in a separate clause of a CREATE TABLE statement

  • e.g., FOREIGN KEY

  • some constraints can be either column or table constraints

    • e.g., PRIMARY KEY can appear either in the column declaration or a separate CREATE TABLE clause
      • on a composite column, must be defined as a table constraint

UNIQUE Constraint

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

  • when applied to a single column,
    • may appear either in the column declaration or a separate clause
  • when applied to a group of columns,
    • is a table constraint and must appear in a separate column
  • MySQL creates an index for each UNIQUE constraint
    • index stores the values of the unique column in sorted order
    • when new values are inserted or updated,
      • MySQL searches the index to quickly determine if the new value is unique

CHECK Constraint

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

  • constraint is violated when the expression is FALSE
  • satisfies when the expression is TRUE or NULL
  • when expression contains one column,
    • CHECK may appear either in the column declaration or a separate clause
  • when expression contains multiple columns,
    • is a table constraint and must be a separate clause

CHECK Constraint

CREATE TABLE Department (

Code TINYINT UNSIGNED,
Name VARCHAR(20),
ManagerID SMALLINT,
AdminAssistID SMALLINT,
Size VARCHAR(6) CHECK (Size IN (‘small’, ‘medium’, ‘large’)),
PRIMARY KEY (Code),
CHECK (ManagerID >= 1000 AND ManagerID <> AdminAssistID)
);

Constraint Names

The CONSTRAINT keyword is used to name a table constraint.

  • syntax: CONSTRAINT name declaration
    • if no name provided, database generates a default name
  • names appear in error messages when violated
  • most column constraints cannot be named
    • except for CHECK

Adding and Dropping Constraints

Add or drop constraints with the ALTER TABLE TableName followed by ADD, DROP, or CHANGE clause.

  • unnamed constraints are added or dropped with a CHANGE clause:
    • CHANGE CurrentColumnName NewColumnName NewDataType [ConstraintDeclaration]
  • named constraints are added with an ADD clause:
    • ADD [CONSTRAINT ConstraintName] PRIMARY KEY (Column1, Columne2 ...)
    • ADD [CONSTRAINT ConstraintName] FOREIGN KEY (Column1, Column2 ...) REFERENCES TableName (Column)
    • ADD [CONSTRAINT ConstraintName] UNIQUE (Column1, Column2 ...)
    • ADD [CONSTRAINT ConstraintName] CHECK (expression)
  • named constraints are dropped with a DROP clause:
    • DROP PRIMARY KEY
    • DROP FOREIGN KEY ConstraintName
    • DROP INDEX ConstraintName (drops UNIQUE constraints)
    • DROP CHECK ConstraintName
    • DROP CONSTRAINT ConstraintName (drops any named constraint)
  • dropping a table fails when a foreign key constraint refers to the table’s primary key
    • before dropping the table, the foreign key constraint or the foreign key table must be dropped