G Gokul

Posted on Aug 12

Constraints in PostgreSQL

#constraints #postgres #payilagam #sql

Constraints:

Constraints in PostgreSQL are rules enforced on data columns and tables to prevent invalid data from entering the database.
They ensure data accuracy, reliability, and consistency across your database schema.

Types of constraints:

1. CHECK Constraint:

Validates that values in a column satisfy a specific boolean expression before they are committed.
If the expression evaluates to FALSE, the database rejects the operation.

create table products(product_no integer, name text, price numeric CHECK (price>0));

output:
CREATE TABLE

insert into products values(1, ‘rice’, 100);
insert 0 1
insert into products values(2, ‘bread’, -20);

output:
ERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (2, bread, -20).

we can also create several CHECK constraint in one table:

create table products3(
product_no integer, 
name text, 
price numeric check (price>0), 
discount integer check (discount > 0), 
CHECK (price > discount)
);

assigning name to constraint:

we can assign a specific name to constraint
use the key word CONSTRAINT followed by an identifier followed by the constraint definition.

create table products2(product_no integer, name text, price numeric CONSTRAINT PRICE check (price>0));

2. UNIQUE Constraint:

Guarantees that all values in a column or a group of columns are distinct across all rows in the table.
It automatically creates a unique b-tree index behind the scenes.

create Table mobile2(
  imei_no integer,  
  brand text, 
  price numeric,
  unique (imei_no)
  );

output:
CREATE TABLE

insert into mobile2 values(1,'realme',23000);

output:
INSERT 0 1

insert into mobile2 values(1,'redme',23000);

output:
ERROR: duplicate key value violates unique constraint "mobile2_imei_no_key"
DETAIL: Key (imei_no)=(1) already exists.

Multiple unique constraints:

create Table mobile2(
  imei_no integer,
  sim_no integer,  
  brand text, 
  price numeric,
  unique (imei_no, sim_no)
  );

3. NOT NULL Constraint:

Ensures that a column cannot accept NULL values.
You must provide a valid value for this column during insertion or update operations.

create table contacts(mobile_no integer not null, name text not null);

output:
CREATE TABLE

insert into contacts values(null, null);

output:
ERROR: null value in column "mobile_no" of relation "contacts" violates not-null constraint
DETAIL: Failing row contains (null, null).

we can also create several NOT NULL constraint in one table:

CREATE TABLE products1 (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

output:
CREATE TABLE

insert into products3 values(100, 0, 0);

output:
ERROR: new row for relation "products1" violates check constraint "products1_price_check"
DETAIL: Failing row contains (100, 0, 0).

4. PRIMARY KEY Constraint:

Uniquely identifies each row in a table.
A table can have only one primary key, which acts as a combination of NOT NULL and UNIQUE constraints.

CREATE TABLE products4 (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

output:
CREATE TABLE

insert into products4 values(null,'aaa',45);

output:
ERROR: null value in column "product_no" of relation "products4" violates not-null constraint
DETAIL: Failing row contains (null, aaa, 45).

Multiple primary key constraints:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

insert into example values(1,1,1);

output:
INSERT 0 1

insert into example values(1,1,null);

output:
ERROR: null value in column "c" of relation "example" violates not-null constraint
DETAIL: Failing row contains (1, 1, null).

5. FOREIGN KEY (Referential Integrity) Constraint:

Establishes a link between columns in two tables, ensuring that the value in the child table must exist in the referenced column of the parent table.

CREATE TABLE customers(
customer_id INT GENERATED ALWAYS AS IDENTITY,
customer_name VARCHAR(255) NOT NULL,
PRIMARY KEY(customer_id)
);

output:
CREATE TABLE

insert into customers(customer_name) values('aaa'),('bbb'),('ccc');

output:
INSERT 0 3

select * from customers;

output:
customer_id | customer_name
-------------+---------------
1 | aaa
2 | bbb
3 | ccc
(3 rows)

The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.

In this syntax:

The type can be SMALLINT, INT, or BIGINT.
The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column.
If you attempt to insert (or update) values into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.

CREATE TABLE contacts2(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
          REFERENCES customers(customer_id)
      ON DELETE CASCADE
);

output:
CREATE TABLE

INSERT INTO contacts2(customer_id, contact_name, phone, email)
VALUES(1,'Rajini','123456','rajini@gmail.com'),
(1,'Kamal','45678','kamal@gmail.com'),
(2,'Vijayakanth','34567','vijayakanth@gmail.com');

output:
INSERT 0 3

select * from contacts2;

output:

contact_id	customer_id	contact_name	phone	email
1	1	Rajini	123456	rajini@gmail.com
2	1	Kamal	45678	kamal@gmail.com
3	2	Vijayakanth	34567	vijayakanth@gmail.com

(3 rows)

DELETE FROM customers WHERE customer_id = 1;

output:
DELETE 1

select * from contacts2;

select * from customers;
**output:**

customer_id	customer_name
2	bbb
3	ccc

(2 rows)

ON DELETE CASCADE automatically removes child records if the corresponding parent record is deleted.

6. EXCLUSION Constraint:(TBD)

An advanced PostgreSQL-specific constraint.
It ensures that if any two rows are compared on specified columns using specific operators, not all of the comparisons will return true.
This is highly useful for preventing scheduling overlaps.

views in postgresql:

A view in PostgreSQL is a virtual table that represents the result of a pre-defined, saved SQL query.
It does not physically store data on its own disk space; instead, it dynamically fetches live data from the underlying base tables every time you query it.

to create a view:

create view v as select * from mobile2;

output:
CREATE VIEW

to read a view

select * from v;

output:
imei_no | brand | price
---------+--------+-------
1 | realme | 23000
(1 row)

to create views from multiple table:

create view v2 as select * from mobile2 union select * from products;

output:
CREATE VIEW

select * from v2;

imei_no	brand	price
1	realme	23000
1	rice	100

(2 rows)

to delete a view:

drop view v;

output:
DROP VIEW

select * from v;

output:
ERROR: relation "v" does not exist
LINE 1: select * from v;

Index in postgresql:

An index in PostgreSQL is a separate data structure that enhances the speed of data retrieval from a table at the cost of additional write overhead and storage space.
Without an index, PostgreSQL must perform a full table scan, reading every single row to find a match.

without index:

select * from mobile2;

imei_no	brand	price
1	realme	23000

(1 row)

explain select * from mobile2;

                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on mobile2  (cost=0.00..18.50 rows=850 width=68)
(1 row)

with create index:
it increases the database performance

create index new_brand on mobile2(brand);

CREATE INDEX

explain select * from mobile2;

                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on mobile2  (cost=0.00..1.01 rows=1 width=68)
(1 row)

EXPLAIN statement:

The EXPLAIN statement in PostgreSQL displays the execution plan generated by the query planner for a given SQL statement.
It details how tables will be scanned (e.g., sequential or index scans), join algorithms used, and cost estimates, allowing you to troubleshoot and optimize slow queries.

Special Indexing Strategies:(tbd)

Partial Indexes CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';
Expression Indexes CREATE INDEX idx_lower_email ON users (LOWER(email));
Covering Indexes (INCLUDE) CREATE INDEX idx_order_user ON orders (user_id) INCLUDE (total_amount);

DEV Community

Constraints in PostgreSQL

Constraints:

Types of constraints:

1. CHECK Constraint:

2. UNIQUE Constraint:

3. NOT NULL Constraint:

4. PRIMARY KEY Constraint:

5. FOREIGN KEY (Referential Integrity) Constraint:

views in postgresql:

Index in postgresql:

EXPLAIN statement:

Top comments (0)