Constraints prevent database operations that deteriorate the integrity of the data.
## unique constraint
Unique constraints enforce uniqueness for any column, and are enforced by default with primary keys.
```SQL
-- SPECIFY IN CREATE TABLE
CREATE TABLE <tablenamd> (
col1 TEXT,
col2 TEXT,
CONSTRAINT <constraint_name> UNIQUE (col1)
);
-- OR USE ALTER TABLE
ALTER TABLE <tablename> ADD CONSTRAINT <contraint_name> UNIQUE (<col>);
```
## check constraint
Use check for validation of database inputs. See also [enumerators](#enumerators).
```SQL
-- SPECIFY IN CREATE TABLE
CREATE TABLE <tablename> (
gender TEXT CHECK (gender IN ('Female', 'Male'))
);
-- OR USE ALTER TABLE
ALTER TABLE <tablename> ADD CONSTRAINT <contraint_name> CHECK (gender IN ('Female', 'Male'));
```
## on conflict do nothing
Use the `ON CONFLICT` command combined with `DO NOTHING` to skip records that violate constraints. For example, inserting a record with an existing primary key violates the `NOT NULL` constraint and will fail.
```SQL
INSERT INTO <tablename> (first_name, last_name)
VALUES ('John', 'Doe')
ON CONFLICT (id) DO NOTHING;
```
This is useful when updating a table where some of the records in the data source are already in the table.
# upsert
Use `ON CONFLICT` command combined with `DO UPDATE` to update a record when a constraint is violated. This is commonly referred to as an "upsert".
```SQL
INSERT INTO <tablename> (first_name, last_name)
VALUES ('John', 'Doe')
ON CONFLICT (id) DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name;
```
This is useful when a user is editing a record in your application or when you have edited many records in the data source.
Some caveats
- If using a partial index, you must constrain to only the indexed part of the table using a `WHERE` statement (see [here](https://stackoverflow.com/questions/42022362/no-unique-or-exclusion-constraint-matching-the-on-conflict)).
CASCADE
NO ACTION
SET NULL
SET DEFAULT
RESTRICT