What are the different types of constraints in PostgreSQL?

In PostgreSQL, constraints are used to define rules for columns in tables, ensuring the accuracy and reliability of data within the database. PostgreSQL supports various types of constraints, and here are some main types:

1. PRIMARY KEY Constraint:

   • This constraint uniquely identifies each row in a database table. Each table can have one primary key, and the values in the primary key column must be unique and not NULL.
   • For example, the employee ID column in the employees table can be set as PRIMARY KEY to ensure each employee has a unique ID.

2. FOREIGN KEY Constraint:

   • Used to establish a link between two tables, ensuring that data in one table references valid data in another table.
   • For instance, if the department ID is the primary key in the departments table, it can be used as a FOREIGN KEY in the employees table, ensuring that the department ID in the employees table exists in the departments table.

3. UNIQUE Constraint:

   • Ensures that values in a single column or a combination of columns are unique within the database table.
   • For example, the email column in the employees table can be set as UNIQUE to prevent duplicate email addresses.

4. CHECK Constraint:

   • Allows specifying a condition that data in the table must satisfy.
   • For example, you can enforce that an employee's age must be at least 18: CHECK (age >= 18).

5. NOT NULL Constraint:

   • Ensures that values in a column are never NULL.
   • For example, in the employees table, the name and employee ID columns can be set as NOT NULL to require these fields when entering data.

6. EXCLUSION Constraint:

   • Used to ensure that when any two rows in the table are compared using the same operator, at least one comparison result is FALSE or NULL.
   • For example, in the meeting room reservation table, an EXCLUSION constraint on the time period ensures no overlapping time slots.

These constraints can be defined during table creation or added afterward using the ALTER TABLE command. Proper implementation of these constraints significantly enhances data integrity and accuracy in the database.