Constraints

Postgres constraints are rules enforced on data columns within a table to maintain data integrity and prevent the insertion of invalid data.

Create a table with primary & unique constraints

$$
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL `PRIMARY` KEY,        
  emp_name VARCHAR(50) NOT NULL,    
  emp_ssn VARCHAR (30) NOT NULL `UNIQUE`, 
  emp_salary NUMERIC(9,2) NOT NULL
);
$$

// Creates a new table with primary & unique key constraints (SQL)

Primary Key Constraint: Enforces the uniqueness of a column or a set of columns, ensuring that each row in a table is uniquely identified.

Unique Constraint: Ensures that all values in a column or a set of columns are distinct, except for null values.

Avoid duplicate records

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Rohit', '1234', 5000.0);
INSERT 0 1

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values (Mason, '1234', 7500.0);
ERROR:  duplicate key value violates unique constraint "employee_emp_ssn_key"
DETAIL:  Key (emp_ssn)=(1234) already exists.

// Insert records in a table with unique key constraints specified (SQL)

This table uses emp_id as primary key column (Keyword “primary”) and a unique constraint (Keyword “unique”) is specified on employee social security number (emp_ssn) to avoid duplicate ssn being entered.

Create a table with check constraint

$$
CREATE TABLE orders(
  ord_no integer,
  ord_date date,
  ord_qty numeric,
  ord_amount numeric `CHECK (ord_amount>0)`
);
$$

// Creates a new table with check constraint specified (SQL)

insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (1, '2019-08-29', 1, 10);
INSERT 0 1 

insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (2, '2019-08-29', 1, 0);
ERROR:  new row for relation "orders" violates check constraint "orders_ord_amount_check"
DETAIL:  Failing row contains (2, 2019-08-29, 1, 0).

// Insert records in table with check constraints specified (SQL)

Check constraint (Keyword “check”) is specified on order amount (ord_amount > 0) on table so any records with ord_amount <=0 will fail to insert

Check Constraint: Verifies that all values in a column or a set of columns satisfy a specified condition or expression.

Define relation between two tables (foreign key constraint)

$$
CREATE TABLE IF NOT EXISTS department (
  dept_id SERIAL PRIMARY KEY,        
  dept_name VARCHAR(50) NOT NULL
);
$$
CREATE TABLE

$$
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        
  emp_name VARCHAR(50) NOT NULL,    
  emp_ssn VARCHAR (30) NOT NULL UNIQUE, 
  emp_salary NUMERIC(9,2) NOT NULL,
  emp_dept_id INTEGER `REFERENCES` department (dept_id)    -- Foreign Key
);
$$
CREATE TABLE

// Creates table department & employee and defines a relation of an employee to department using foreign key (“REFERENCES”) (SQL)

Foreign Key Constraint: Establishes a link between data in two tables, enforcing referential integrity by preserving the relationships between the linked tables.

Check constraints on a table (using \d option)

\d employee;
                                     Table "public.employee"
   Column    |         Type          |                         Modifiers                         
-------------+-----------------------+-----------------------------------------------------------
 emp_id      | integer               | not null default nextval('employee_emp_id_seq'::regclass)
. . .
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "employee_emp_ssn_key" UNIQUE CONSTRAINT, btree (emp_ssn)
Foreign-key constraints:
    "employee_emp_dept_id_fkey" FOREIGN KEY (emp_dept_id) REFERENCES department(dept_id)

// Display constraints on a table (using \d option) (psql)