Constraints

# Constraints

SQL allows you to define constraints on columns and tables. Constraints give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised.

- column constraint: `product_no integer UNIQUE`
- table constraints: `UNIQUE (product_no)`
- constraints can have names: `price numeric CONSTRAINT positive_price CHECK (price > 0)`

# Unique constraints

- still possible to have multiple null values
    - this behavior can be changed: `product_no integer UNIQUE NULLS NOT DISTINCT`

## handled

- uniqueness of single column
- uniqueness of groups of columns
- column and table constraint style

## un-handled

- Null-values contained in columns that are part of UNIQUE constraint
- `NULLS NOT DISTINCT` add-on

```
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);
```

This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique.

# Not-Null constraints

- can only be column constraints

# Primary keys

- `PRIMARY KEY` is the same as `UNIQUE NOT NULL`

Primary keys can be defined like:

```
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
```

or like:

```
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
```

or like:

```
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);
```

# Foreign keys

## short form

```
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);
```

can be shortened to (in this case the primary key of products is used as reference):

```
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);
```

## group of columns as foreign key

A foreign key can also constrain and reference a group of columns:

```
CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
```

## multiple foreign keys

-  used to implement many-to-many relationships between tables

```
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
```

```
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);
```

```
CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
```

- notice that the primary key overlaps with the foreign keys in the last table

# Check constraint

...

# Exclusion constraint

...