> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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`](/docs/stable/check) | Values must return `TRUE` or `NULL` for a Boolean expression. |
| [`DEFAULT` value](/docs/stable/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`](/docs/stable/foreign-key) | Values must exactly match existing values from the column it references. |
| [`NOT NULL`](/docs/stable/not-null) | Values may not be `NULL`. |
| [`PRIMARY KEY`](/docs/stable/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](/docs/stable/indexes.md) for the table using the constrained columns. |
| [`UNIQUE`](/docs/stable/unique) | Each non-`NULL` value must be unique. This also automatically creates an [index](/docs/stable/indexes.md) 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`:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  > 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`:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  > 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`](/docs/stable/alter-table#add-constraint). For example, this statement adds the `UNIQUE` constraint to `baz.id`:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    > ALTER TABLE baz ADD CONSTRAINT id_unique UNIQUE (id);
    ```

  * `DEFAULT` values and `NOT NULL` constraints can be added through [`ALTER TABLE...ALTER COLUMN`](/docs/stable/alter-table#set-or-change-a-default-value). For example, this statement adds the [`DEFAULT` value constraint](/docs/stable/default-value) to `baz.bool`:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    > ALTER TABLE baz ALTER COLUMN bool SET DEFAULT true;
    ```

  * [`PRIMARY KEY`](/docs/stable/primary-key) constraints can be added with [`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint)/[`ADD PRIMARY KEY`](/docs/stable/alter-table) in the following circumstances:

    * A [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint) statement precedes the `ADD CONSTRAINT`/`ADD PRIMARY KEY` statement in the same transaction. For examples, see [Add constraints](/docs/stable/alter-table.md#add-constraints) and [Drop constraints](/docs/stable/alter-table.md#drop-constraints).
    * The current [primary key is on `rowid`](/docs/stable/indexes#creation), 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE foo (a INT CONSTRAINT another_name PRIMARY KEY);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`](/docs/stable/show-constraints) or [`SHOW CREATE`](/docs/stable/show-create).

### Remove constraints

The procedure for removing a constraint depends on its type:

| Constraint Type | Procedure |
| - | - |
| [`CHECK`](/docs/stable/check) | Use [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint). |
| [`DEFAULT` value](/docs/stable/default-value) | Use [`ALTER COLUMN`](/docs/stable/alter-table#remove-default-constraint). |
| [`FOREIGN KEY`](/docs/stable/foreign-key) | Use [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint). |
| [`NOT NULL`](/docs/stable/not-null) | Use [`ALTER COLUMN`](/docs/stable/alter-table#remove-not-null-constraint). |
| [`PRIMARY KEY`](/docs/stable/primary-key) | Primary key constraints can be dropped with [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint) if an [`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint) statement follows the `DROP CONSTRAINT` statement in the same transaction. |
| [`UNIQUE`](/docs/stable/unique) | The `UNIQUE` constraint cannot be dropped directly.  To remove the constraint, [drop the index](/docs/stable/drop-index.md) 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`](/docs/stable/check) | [Issue a transaction](/docs/stable/transactions.md#syntax) that adds a new `CHECK` constraint ([`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint)), and then remove the existing one ([`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint)). |
| [`DEFAULT` value](/docs/stable/default-value) | The `DEFAULT` value can be changed through [`ALTER COLUMN`](/docs/stable/alter-table#alter-column). |
| [`FOREIGN KEY`](/docs/stable/foreign-key) | [Issue a transaction](/docs/stable/transactions.md#syntax) that adds a new `FOREIGN KEY` constraint ([`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint)), and then remove the existing one ([`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint)). |
| [`NOT NULL`](/docs/stable/not-null) | The `NOT NULL` constraint cannot be changed, only added and removed with [`ALTER COLUMN`](/docs/stable/alter-table#alter-column). |
| [`PRIMARY KEY`](/docs/stable/primary-key) | To change a primary key, use an [`ALTER TABLE ... ALTER PRIMARY KEY`](/docs/stable/alter-table#alter-primary-key) statement. When you change a primary key with [`ALTER PRIMARY KEY`](/docs/stable/alter-table#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`](/docs/stable/alter-table#drop-constraint)/[`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint) to change the primary key. |
| [`UNIQUE`](/docs/stable/unique) | [Issue a transaction](/docs/stable/transactions.md#syntax) that adds a new `UNIQUE` constraint ([`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint)), and then remove the existing one ([`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint)). |

## See also

* [`CREATE TABLE`](/docs/stable/create-table)
* [`ADD CONSTRAINT`](/docs/stable/alter-table#add-constraint)
* [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint)
* [`SHOW CONSTRAINTS`](/docs/stable/show-constraints)
* [`SHOW CREATE`](/docs/stable/show-create)
* [`ALTER PRIMARY KEY`](/docs/stable/alter-table#alter-primary-key)
* [`ALTER TABLE`](/docs/stable/alter-table)
* [`ALTER COLUMN`](/docs/stable/alter-table#alter-column)