1.12 Constraints

Question 1

constraint

Answer 1

A constraint is a rule that governs allowable values in a database. Constraints 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.

Question 2

column constraint

Answer 2

A column constraint appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single column.

Question 3

table constraint

Answer 3

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

Question 4

UNIQUE constraint

Answer 4

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

Can be used as a column constraint (single collum), which cannot be named. Or used as a table contraint (multiple columns), which can be named.

Name VARCHAR(20) UNIQUE

CONSTRAINT UniqueNameMgr UNIQUE (Name, ManagerID)

Question 5

CHECK

Answer 5

The 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.

Only column constraint that can be named.

CONSTRAINT CheckManager CHECK (ManagerID > 999)

Question 6

CONSTRAINT keyword usage

Answer 6

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

CONSTRAINT DepartmentKey PRIMARY KEY (Code)

Question 7

Add or remove a constraint

Answer 7

ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause.

ALTER TABLE Employee
ADD CONSTRAINT HireCheck CHECK (HireDate < ‘2000-02-14’);

Unnamed constraints such as NOT NULL and DEFAULT are added or dropped with a CHANGE clause:

CHANGE CurrentColumnName NewColumnName NewDataType [ConstraintDeclaration]