Skip to content

Latest commit

 

History

History
125 lines (90 loc) · 7.32 KB

constraints.md

File metadata and controls

125 lines (90 loc) · 7.32 KB
title summary toc
Constraints
Constraints offer additional data integrity by enforcing conditions on the data within a column.
true

Constraints offer additional data integrity by enforcing conditions on the data within a column. Whenever values are manipulated (inserted, deleted, or updated), constraints are checked and modifications that violate constraints are rejected.

For example, the UNIQUE constraint requires that all values in a column be unique from one another (except NULL values). If you attempt to write a duplicate value, the constraint rejects the entire statement.

Supported constraints

Constraint Description
CHECK Values must return TRUE or NULL for a Boolean expression.
DEFAULT value If a value is not defined for the constrained column in an INSERT statement, the DEFAULT value is written to the column.
FOREIGN KEY Values must exactly match existing values from the column it references.
NOT NULL Values may not be NULL.
PRIMARY KEY Values must uniquely identify each row (one per table). This behaves as if the NOT NULL and UNIQUE constraints are applied, as well as automatically creates an index for the table using the constrained columns.
UNIQUE Each non-NULL value must be unique. This also automatically creates an index for the table using the constrained columns.

Using constraints

Add constraints

How you add constraints depends on the number of columns you want to constrain, as well as whether or not the table is new.

  • One column of a new table has its constraints defined after the column's data type. For example, this statement applies the PRIMARY KEY constraint to foo.a:

    {% include copy-clipboard.html %}

    > CREATE TABLE foo (a INT PRIMARY KEY);

  • Multiple columns of a new table have their constraints defined after the table's columns. For example, this statement applies the PRIMARY KEY constraint to foo's columns a and b:

    {% include copy-clipboard.html %}

    > CREATE TABLE bar (a INT, b INT, PRIMARY KEY (a,b));

    {{site.data.alerts.callout_info}} The DEFAULT and NOT NULL constraints cannot be applied to multiple columns. {{site.data.alerts.end}}

  • Existing tables can have the following constraints added:

    • CHECK, FOREIGN KEY, and UNIQUE constraints can be added through ALTER TABLE...ADD CONSTRAINT. For example, this statement adds the UNIQUE constraint to baz.id:

      {% include copy-clipboard.html %}

      > ALTER TABLE baz ADD CONSTRAINT id_unique UNIQUE (id);

    • DEFAULT values and NOT NULL constraints can be added through ALTER TABLE...ALTER COLUMN. For example, this statement adds the Default Value constraint to baz.bool:

      {% include copy-clipboard.html %}

      > ALTER TABLE baz ALTER COLUMN bool SET DEFAULT true;

    • PRIMARY KEY constraints can be added with ADD CONSTRAINT/ADD PRIMARY KEY in the following circumstances:

      • A DROP CONSTRAINT statement precedes the ADD CONSTRAINT/ADD PRIMARY KEY statement in the same transaction. For examples, see the ADD CONSTRAINT and DROP CONSTRAINT pages.
      • The current primary key is on rowid, the default primary key created if none is explicitly defined at table creation.
      • The ADD CONSTRAINT/ADD PRIMARY KEY is in the same transaction as a CREATE TABLE statement with no primary key defined.

Order of constraints

The order in which you list constraints is not important because constraints are applied to every modification of their respective tables or columns.

Name constraints on new tables

You can name constraints applied to new tables using the CONSTRAINT clause before defining the constraint:

{% include copy-clipboard.html %}

> CREATE TABLE foo (a INT CONSTRAINT another_name PRIMARY KEY);

{% include copy-clipboard.html %}

> CREATE TABLE bar (a INT, b INT, CONSTRAINT yet_another_name PRIMARY KEY (a,b));

View constraints

To view a table's constraints, use SHOW CONSTRAINTS or SHOW CREATE.

Remove constraints

The procedure for removing a constraint depends on its type:

Constraint Type Procedure
CHECK Use DROP CONSTRAINT.
DEFAULT value Use ALTER COLUMN.
FOREIGN KEY Use DROP CONSTRAINT.
NOT NULL Use ALTER COLUMN.
PRIMARY KEY Primary key constraints can be dropped with DROP CONSTRAINT if an ADD CONSTRAINT statement follows the DROP CONSTRAINT statement in the same transaction.
UNIQUE The UNIQUE constraint cannot be dropped directly. To remove the constraint, drop the index that was created by the constraint, e.g., DROP INDEX my_unique_constraint.

Change constraints

The procedure for changing a constraint depends on its type:

Constraint Type Procedure
CHECK Issue a transaction that adds a new CHECK constraint (ADD CONSTRAINT), and then remove the existing one (DROP CONSTRAINT).
DEFAULT value The DEFAULT value can be changed through ALTER COLUMN.
FOREIGN KEY Issue a transaction that adds a new FOREIGN KEY constraint (ADD CONSTRAINT), and then remove the existing one (DROP CONSTRAINT).
NOT NULL The NOT NULL constraint cannot be changed, only added and removed with ALTER COLUMN.
PRIMARY KEY To change a primary key, use an ALTER TABLE ... ALTER PRIMARY KEY statement.

When you change a primary key with ALTER PRIMARY KEY, the old primary key index becomes a secondary index. If you do not want the old primary key to become a secondary index, use DROP CONSTRAINT/ADD CONSTRAINT to change the primary key.
UNIQUE Issue a transaction that adds a new UNIQUE constraint (ADD CONSTRAINT), and then remove the existing one (DROP CONSTRAINT).

See also