PostgreSQL Constraints

What is Constraints? 
Certain rules on on data columns on table is called Constraints  These rules are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.

There are two ways to define constraints: 
1.Table constraints-A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column.
2.Column constraints-A column constraint is defined as part of a column definition. 

In this tutorial, you will learn how to use the Constraint CREATE TABLE statement when you  create new tables.

CREATE TABLE order_details
( column1 integer CONSTRAINT order_details_pk PRIMARY KEY,
 column2 integer NOT NULL, 
);                                                                                       

• First, you specify the name of the new table after the CREATE TABLE clause. The TEMPORARY keyword is for creating a temporary table, which we will discuss in the temporary table tutorial.
• Next, you list the column name, its data type, and column constraint. You can have multiple columns in a table, each column is separated by a comma (,). The column constraint defines the rules for the column e.g.,  NOT NULL.
• Then, after the column list, you define a table-level constraint that defines rules for the data in the table.
• After that, you specify an existing table from which the new table inherits. It means the new table contains all columns of the existing table and the columns defined in the CREATE TABLE statement. This is a PostgreSQL’s extension to SQL.

The following are the commonly used column constraints in PostgreSQL:

Contraints name	Explanation
PRIMARY KEY	this constraint is the combination of NOT NULL and UNIQUE constraints. You can define one column as PRIMARY KEY by using column-level constraint. In case the primary key contains multiple columns, you must use the table-level constraint.
UNIQUE	the value of the column must be unique across the whole table. However, the column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.
CHECK	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.
NOT NULL	the value of the column cannot be NULL.
DEFAULT	Assign a literal constant default value for the column name, arrange for the default value of column did to be generated by selecting the next value of a sequence object, and make the default value of modtime be the time at which the row is inserted
REFERENCES	Constrains the value of the column that exists in a column in another table. You use REFERENCES to define the foreign key constraint.

1.Table constraints

Define a unique table constraint for the table unitab. Unique table constraints can be defined on one or more columns of the table:

CREATE TABLE unitab (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

Define a check table constraint:

CREATE TABLE checktab (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

Define a primary key table constraint for the table primtab:

CREATE TABLE primtab (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

Define a unique constraint for the name column table constraints:

CREATE TABLE uniqtab (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);

2.Column constraints

--Create table films and table distributors:

CREATE TABLE primcol (
    code        char(5) CONSTRAINT prikey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

CREATE TABLE notcol (
     did    integer PRIMARY KEY DEFAULT nextval('k3'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);
ERROR:  relation "k3" does not exist

note that nextval('films') is a table name

CREATE TABLE notcol (
     did    integer PRIMARY KEY DEFAULT nextval('primcol'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

Define a check column constraint:

CREATE TABLE checkcol (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

Assign a literal constant default value for the column name, arrange for the default value of column did to be generated by selecting the next value of a sequence object, and make the default value of modtime be the time at which the row is inserted:

CREATE TABLE defaultcol(
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

Define two NOT NULL column constraints on the table notcol, one of which is explicitly given a name:

CREATE TABLE notcol (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

Define a unique constraint for the name column:

CREATE TABLE uniqcol (
    did     integer,
    name    varchar(40) UNIQUE
);

Define a primary key constraint for table distributors. The following two examples are equivalent, the first using the table constraint syntax, the second the column constraint syntax:

CREATE TABLE primarytable (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);

CREATE TABLE primarycolumn (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

create a composite type and a typed table:

CREATE TYPE employee_type AS (name text, salary numeric);

CREATE TABLE employees OF employee_type (
    PRIMARY KEY (name),
    salary WITH OPTIONS DEFAULT 1000
);

mostly used constraints:
user_id      – primary key
username   – unique and not null
password   – not null
email         – unique and not null
created_on – not null
last_login    – null
place,salary -with option default 'india',salary WITH OPTIONS DEFAULT 1000

how to checking the existence of a constraint?

postgres=# \d primtab 
             Table "public.primtab"
  Column   |          Type           | Modifiers 
-----------+-------------------------+-----------
 code      | character(5)            | not null
 title     | character varying(40)   | not null
 did       | integer                 | 
 date_prod | date                    | 
 kind      | character varying(10)   | 
 len       | interval hour to minute | 
Indexes:
    "code_title" PRIMARY KEY, btree (code, title)

if you want see all constraint name and table name(relname) you need to join pg_stat_all_tables and pg_constraint views:

postgres=# select a.conname,a.contype,a.conrelid,b.relname,b.relid from pg_constraint a,pg_stat_all_tables b where a.conrelid=b.relid; 
         conname         | contype | conrelid |    relname     | relid 
-------------------------+---------+----------+----------------+-------
 prikey                  | p       |    24676 | films          | 24676
 production              | u       |    24697 | k              | 24697
 distributorc_did_check  | c       |    24702 | distributorc   | 24702
 code_title              | p       |    24710 | primtab        | 24710
 con1                    | c       |    24706 | distributorsct | 24706
 distributors_pkey       | p       |    24690 | distributors   | 24690
 distributors_name_check | c       |    24690 | distributors   | 24690
(7 rows)