PostgreSQL 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. This applies even if the value came from the default value definition.

Check Constraints
Not-Null Constraints
Unique Constraints
Primary Keys
Foreign Keys

Check Constraints:
A check constraint is the most generic constraint type. It allows you to specify that the value in
a certain column must satisfy a Boolean (truth-value) expression. For instance, to require positive
product prices, you could use:

Syntax:

CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);

Not-Null ConstraintsA not-null constraint simply specifies that a column must not assume the null value. A syntax example:

Syntax:

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

Unique Constraints
Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table.

Syntax:

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

Primary Keys
A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.

Syntax:

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

Foreign Keys
A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.We say this maintains the referential integrity between two related tables. Say you have the product table that we have used several times already:

Syntax:

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

Powered by k2schools