# Constraints

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

      > 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));

Note:
The `DEFAULT` and `NOT NULL` constraints cannot be applied to multiple columns.

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

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

### 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 Keys cannot be removed. However, you can move the table's data to a new table with this process.
`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 CASCADE` (note that `CASCADE` is required for dropping indexes used by unique constraints).

### 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 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. Drop the old table, and then rename the new table to the old name. This cannot be done transactionally.

## See also

- `CREATE TABLE`
- `ADD CONSTRAINT`
- `DROP CONSTRAINT`
- `SHOW CONSTRAINTS`
- `SHOW CREATE`