PostgreSQL-constraints

Constraints:

Constraints are the backbone of reliable database design. They act like safety checks, ensuring that every piece of data stored is valid, consistent, and meaningful.

Types of Constraints

1.PRIMARY KEY

-Ensures each row has a unique identifier.
-No duplicates, no NULL values.

example:

emp_id SERIAL PRIMARY KEY

2.FOREIGN KEY

-Creates a relationship between two tables.
-Ensures values in one table match values in another.

example:

dept_id INT REFERENCES Department(dept_id)

3.UNIQUE

-Guarantees that all values in a column are distinct.

example:

email VARCHAR(150) UNIQUE

4. NOT NULL

-Prevents empty values in a column.

example:

name VARCHAR(100) NOT NULL

5.DEFAULT

-Assigns an automatic value if none is provided during insertion.

example:

status VARCHAR(20) DEFAULT 'Active'

6. CHECK

-Validates data against a condition.

example:

salary NUMERIC(10,2) CHECK (salary > 0)

Example table:

CREATE TABLE Employee (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    dept_id INT REFERENCES Department(dept_id),
    salary NUMERIC(10,2) CHECK (salary > 0),
    status VARCHAR(20) DEFAULT 'Active'
);

Why Constraints Matter

-Data Integrity: Prevents invalid or duplicate entries.
-Consistency: Enforces business rules (e.g., salary must be positive).
-Relationships: Maintains links between tables.
-Reliability: Ensures queries always return valid data.

Next blog is Relationship(joins) in PostgreSql....