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 TABLEstatement - database auto-rejects statements that violate a constraint
- e.g.,
NOT NULLDEFAULTPRIMARY KEYFOREIGN 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 TABLEstatement - e.g.,
NOT NULL
A table constraint governs values in one or more columns.
-
appears in a separate clause of a
CREATE TABLEstatement -
e.g.,
FOREIGN KEY -
some constraints can be either column or table constraints
- e.g.,
PRIMARY KEYcan appear either in the column declaration or a separateCREATE TABLEclause- on a composite column, must be defined as a table constraint
- e.g.,
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
UNIQUEconstraint- 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
TRUEorNULL - when expression contains one column,
CHECKmay 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
- except for
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
CHANGEclause:CHANGE CurrentColumnName NewColumnName NewDataType [ConstraintDeclaration]
- named constraints are added with an
ADDclause: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
DROPclause:DROP PRIMARY KEYDROP FOREIGN KEY ConstraintNameDROP INDEX ConstraintName(drops UNIQUE constraints)DROP CHECK ConstraintNameDROP 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