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)