PostgreSQL Constraints

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

Types Of Constraints:

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

Check Constraints:

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

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

Null and Not-Null Constraints:

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

example :
CREATE TABLE regan (
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 areeb (
id integer UNIQUE,
name text NOT NULL,
age numeric CHECK (age > 18));

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

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

Primary Key:

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 gnanavel (
id integer PRIMARY KEY,
name text NOT NULL,
UNIQUE (id));

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

Foreign Key:

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 bino (
id integer CONSTRAINT constraint_name PRIMARY KEY,
name text NOT NULL,
UNIQUE (id));

CREATE TABLE umar (
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 pearl (b circle, EXCLUDE USING gist (b WITH &&));