PostgreSQL Constraints

CONSTRAINTS:

constraints are used to specify rules for the data in a table.

1. Check Constraints
2. Not-Null and Null Constraints
3. Unique Constraints
4. Primary Keys
5. Foreign Keys
6. Exclusion Constraints

Check Constraints:

The CHECK Constraint enables a condition to check the value being entered into a record.
If the condition evaluates to false, the record violates the constraint and is not entered into the table.

example 1:
CREATE TABLE muthu (
    id integer,
    name text,
    age numeric CHECK (age > 18)
);

example 2:
CREATE TABLE muthu (
    id integer,
    name text,
    age numeric CONSTRAINT constraint_name CHECK (age > 0)
);

Not-Null and Null Constraints:

A not-null constraint simply specifies that a column must not assume the null value.

example :
CREATE TABLE muthu (
    id integer NOT NULL,
    name text NULL,
    age numeric CHECK (age > 18)
);

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.

example 1:
CREATE TABLE muthu (
    id integer UNIQUE,
    name text NOT NULL,
    age numeric CHECK (age > 18)
);

example 2:
CREATE TABLE muthu (
    id integer,
    name text NOT NULL,
    UNIQUE (id)
);

example 3:
CREATE TABLE muthu (
    id integer CONSTRAINT constraint_name UNIQUE,
    name text NOT NULL,
    age numeric CHECK (age > 18)
);

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.

example 1:
CREATE TABLE muthu (
    id integer PRIMARY KEY,
    name text NOT NULL,
    UNIQUE (id)
);

example 2:
CREATE TABLE muthu (
    id integer CONSTRAINT  constraint_name PRIMARY KEY,
    name text NOT NULL,
    UNIQUE (id)
);

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.

example:
CREATE TABLE muthu (
    id integer CONSTRAINT  constraint_name PRIMARY KEY,
    name text NOT NULL,
    UNIQUE (id)
);
CREATE TABLE siva (
    id integer REFERENCES muthu (id),
    name text NOT NULL
);

Exclusion Constraints:

Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

example:
CREATE TABLE muthu (
    b circle,
    EXCLUDE USING gist (b WITH &&)
);