Skip to content

Latest commit

 

History

History
114 lines (79 loc) · 6.77 KB

constraints.md

File metadata and controls

114 lines (79 loc) · 6.77 KB
Raw
title summary toc
Constraints
Constraints offer additional data integrity by enforcing conditions on the data within a column.
false

Constraints provide additional data integrity beyond SQL's type checking. They let you define additional parameters for a column's data, which are checked whenever values are manipulated (inserted, deleted, or updated) and reject modifications that violate the constraint.

For example, the Unique constraint requires that all values in a column be unique from one another (except NULL values). If you attempt to insert 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 Keys 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 value must be unique (though NULL values are still allowed). 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:

    > 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:

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

    {{site.data.alerts.callout_info}}The Default Value 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:

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

    • Default Values can be added through ALTER TABLE...ALTER COLUMN. For example, this statement adds the Default Value constraint to baz.bool:

      > ALTER TABLE baz ALTER COLUMN bool SET DEFAULT true;

    • Primary Key and Not Null constraints cannot be added or changed. However, you can go through this process to migrate data from your current table to a new table with the constraints you want to apply.

Order of Constraints

The order in which you list constraints is not important because all constraints are applied to all values.

Name Constraints on New Tables

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

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

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

Remove Constraints

Constraint Type To remove it...
Check Use DROP CONSTRAINT
Default Value Use ALTER COLUMN
Foreign Keys Use DROP CONSTRAINT
Not Null Use ALTER COLUMN
Primary Key Primary Keys cannot be removed. However, you can move the table's data to a new table with this process.
Unique Use DROP INDEX to remove the index automatically created by the Unique constraint (whose name ends in _key).

Change Constraints

The notion of 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 Keys 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 removed. However, you can move the table's data to a new table with this process.
Primary Key Primary Keys cannot be modified. However, you can move the table's data to a new table with this process.
Unique Issue a transaction that adds a new Unique constraint (ADD CONSTRAINT), and then remove the existing one (DROP CONSTRAINT).

Table Migrations to Add or Change Immutable Constraints

If you want to make a change to an immutable constraint, you can use the following process:

  1. Create a new table with the constraints you want to apply.
  2. Move the data from the old table to the new one using INSERT from a SELECT statement.
  3. Issue a transaction that drops the old table, and then renames the new table to the old name.

See Also