PostgreSQL - CHECK Constraint

In PostgreSQL, the CHECK constraint is used to specify a condition that the data in a table must satisfy. Each row in the table must ensure that the condition specified by the CHECK constraint evaluates to true for the row to be accepted. If the condition evaluates to false for a particular row, that row will be rejected.

Usage:

While creating a table:

You can specify the CHECK constraint when you're creating a table.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    age INTEGER,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    CHECK (age > 18)
);

In the example above:

The salary of an employee must always be greater than 0.
The age of an employee must always be greater than 18.

Adding a CHECK constraint to an existing table:

You can add a CHECK constraint to an existing table using the ALTER TABLE command.

ALTER TABLE employees
ADD CONSTRAINT chk_salary
CHECK (salary > 1000 AND salary < 50000);

This ensures that the salary of an employee is always between 1000 and 50000.

Dropping a CHECK constraint:

If you wish to remove a CHECK constraint, you can use the ALTER TABLE command.

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

Points to Note:

Multiple CHECK Constraints: A table can have multiple CHECK constraints. Each constraint must have a unique name within the table.
NULL Values: If the condition of the CHECK constraint evaluates to NULL, the row will be allowed. For example, if a column has a CHECK constraint that it should be greater than 10, and you insert a NULL value, it will be accepted.
Performance: Be aware that adding a CHECK constraint can impact the performance of data insertion, especially if the condition is complex. However, the impact is generally minimal for most scenarios.
Data Integrity: If you add a CHECK constraint to an existing table, the current data must satisfy the new constraint, or the command will fail.

In summary, the CHECK constraint in PostgreSQL is a powerful feature to ensure data integrity by making sure that the data in the table adheres to specified conditions.

Creating CHECK constraints in PostgreSQL:
- Description: CHECK constraints ensure that values entered into a column meet specified conditions. They are created during table creation or added later.
- Code:
```
CREATE TABLE my_table (
    my_column INTEGER CHECK (my_column > 0)
);
```
Enforcing data integrity with CHECK constraints in PostgreSQL:
- Description: CHECK constraints help maintain data integrity by restricting values that can be inserted or updated.
- Code:
```
ALTER TABLE my_table
ADD CONSTRAINT positive_value CHECK (my_column > 0);
```
Multiple conditions in a CHECK constraint in PostgreSQL:
- Description: You can combine multiple conditions within a single CHECK constraint.
- Code:
```
ALTER TABLE my_table
ADD CONSTRAINT range_constraint CHECK (my_column BETWEEN 1 AND 100);
```

CHECK constraints vs. triggers in PostgreSQL:

Description: CHECK constraints and triggers can both enforce data integrity. CHECK constraints are simpler and more direct, while triggers offer more flexibility but with added complexity.

Code (CHECK Constraint):

CREATE TABLE my_table (
    my_column INTEGER CHECK (my_column > 0)
);

Code (Trigger):

CREATE OR REPLACE FUNCTION check_my_column()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.my_column <= 0 THEN
        RAISE EXCEPTION 'my_column must be greater than 0';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_my_column_trigger
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_my_column();

Disabling and enabling CHECK constraints in PostgreSQL:
- Description: You can temporarily disable CHECK constraints for specific operations and then re-enable them.
- Code (Disable):
```
ALTER TABLE my_table
DISABLE CONSTRAINT positive_value;
```
- Code (Enable):
```
ALTER TABLE my_table
ENABLE CONSTRAINT positive_value;
```
Naming conventions for CHECK constraints in PostgreSQL:
- Description: It's good practice to name your CHECK constraints for clarity and ease of management.
- Code:
```
ALTER TABLE my_table
ADD CONSTRAINT positive_value CHECK (my_column > 0);
```
CHECK constraints on multiple columns in PostgreSQL:
- Description: You can create CHECK constraints on multiple columns to enforce conditions involving more than one column.
- Code:
```
CREATE TABLE my_table (
    column1 INTEGER,
    column2 INTEGER,
    CHECK (column1 + column2 > 100)
);
```