Part 36: Column Constraints: NOT NULL, UNIQUE & CHECK

Constraints guarantee data integrity at the database layer before writes occur.

1. The Power of CHECK Constraints

CHECK constraints enforce custom business rules directly in SQL:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL CHECK (price >= 0.00),
  discount_percentage INT DEFAULT 0 CHECK (discount_percentage BETWEEN 0 AND 100),
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,

  -- Table-level constraint checking relationship between two columns:
  CONSTRAINT check_dates_order CHECK (end_date >= start_date)
);

Key Takeaways

• Use CHECK constraints to validate business rules at the schema level.

In Part 37, we will explore DEFAULT Values and Generated / Computed Columns!