Database constraints and data validation

9523 views

                  -- Primary key (unique, not null identifier)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- Composite primary key
CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id)
);

-- Foreign key (referential integrity)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  product_id INT REFERENCES products(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Foreign key actions
-- ON DELETE CASCADE: Delete child rows when parent deleted
-- ON DELETE SET NULL: Set foreign key to NULL
-- ON DELETE RESTRICT: Prevent deletion if children exist (default)
-- ON DELETE NO ACTION: Same as RESTRICT
-- ON UPDATE CASCADE: Update child rows when parent key changes

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- UNIQUE constraint
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

-- Named unique constraint
ALTER TABLE users
  ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Composite unique constraint
CREATE TABLE user_preferences (
  user_id INT,
  preference_key VARCHAR(50),
  preference_value TEXT,
  UNIQUE (user_id, preference_key)
);

-- NOT NULL constraint
ALTER TABLE users
  ALTER COLUMN email SET NOT NULL;

-- Remove NOT NULL
ALTER TABLE users
  ALTER COLUMN phone DROP NOT NULL;

-- DEFAULT values
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  status VARCHAR(20) DEFAULT 'draft',
  views INT DEFAULT 0,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add default to existing column
ALTER TABLE posts
  ALTER COLUMN status SET DEFAULT 'draft';

-- Remove default
ALTER TABLE posts
  ALTER COLUMN status DROP DEFAULT;

-- CHECK constraints (data validation)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),
  discount_pct INT CHECK (discount_pct BETWEEN 0 AND 100),
  stock INT CHECK (stock >= 0),
  status VARCHAR(20) CHECK (status IN ('active', 'discontinued', 'out_of_stock'))
);

-- Named CHECK constraint
ALTER TABLE products
  ADD CONSTRAINT price_positive CHECK (price > 0);

-- Multi-column CHECK constraint
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  start_time TIMESTAMP NOT NULL,
  end_time TIMESTAMP NOT NULL,
  CHECK (end_time > start_time)
);

-- Complex CHECK constraint
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  hire_date DATE NOT NULL,
  termination_date DATE,
  salary DECIMAL(10,2) CHECK (salary > 0),
  CHECK (termination_date IS NULL OR termination_date > hire_date)
);

-- Drop constraint
ALTER TABLE products
  DROP CONSTRAINT price_positive;

-- Disable constraint (PostgreSQL)
-- Not directly supported, but can use trigger approach

-- View constraints
SELECT
  conname AS constraint_name,
  contype AS constraint_type,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

                  -- Deferrable constraints (check at transaction end)
CREATE TABLE parent (
  id INT PRIMARY KEY
);

CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent(id)
    DEFERRABLE INITIALLY DEFERRED
);

-- Can temporarily violate constraint within transaction
BEGIN;
INSERT INTO child (id, parent_id) VALUES (1, 100);
INSERT INTO parent (id) VALUES (100);
COMMIT;
-- Constraint checked at commit

-- Exclusion constraints (prevent overlaps)
CREATE EXTENSION btree_gist;

CREATE TABLE reservations (
  room_id INT,
  during TSRANGE,
  EXCLUDE USING GIST (
    room_id WITH =,
    during WITH &&
  )
);

-- Prevents overlapping reservations for same room
INSERT INTO reservations VALUES (101, '[2024-01-15 10:00, 2024-01-15 12:00)');
-- This fails (overlaps):
-- INSERT INTO reservations VALUES (101, '[2024-01-15 11:00, 2024-01-15 13:00)');

-- Domain constraints (reusable types)
CREATE DOMAIN email_address AS VARCHAR(255)
  CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

CREATE DOMAIN positive_int AS INT
  CHECK (VALUE > 0);

CREATE DOMAIN percentage AS DECIMAL(5,2)
  CHECK (VALUE BETWEEN 0 AND 100);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email email_address NOT NULL,
  age positive_int,
  completion_rate percentage
);

-- Partial unique index (conditional uniqueness)
CREATE UNIQUE INDEX users_active_email_unique
  ON users(email)
  WHERE deleted_at IS NULL;
-- Allows duplicate emails if user is deleted

-- Unique nulls (multiple NULLs allowed)
CREATE UNIQUE INDEX users_phone_unique
  ON users(phone)
  WHERE phone IS NOT NULL;

-- Generated columns with constraints
CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
  tax_rate DECIMAL(5,4) NOT NULL CHECK (tax_rate BETWEEN 0 AND 1),
  tax_amount DECIMAL(10,2) GENERATED ALWAYS AS (subtotal * tax_rate) STORED,
  total DECIMAL(10,2) GENERATED ALWAYS AS (subtotal + subtotal * tax_rate) STORED,
  CHECK (total > 0)
);

-- Trigger-based validation
CREATE OR REPLACE FUNCTION validate_user_age()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.age < 18 THEN
    RAISE EXCEPTION 'User must be at least 18 years old';
  END IF;

  IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$' THEN
    RAISE EXCEPTION 'Invalid email format';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_user_before_insert
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION validate_user_age();

-- Constraint violation handling
-- Application code example:
/*
try {
  await db.query('INSERT INTO users (email) VALUES ($1)', [email]);
} catch (error) {
  if (error.code === '23505') {  // unique_violation
    throw new Error('Email already exists');
  } else if (error.code === '23503') {  // foreign_key_violation
    throw new Error('Referenced record not found');
  } else if (error.code === '23514') {  // check_violation
    throw new Error('Data validation failed');
  } else if (error.code === '23502') {  // not_null_violation
    throw new Error('Required field missing');
  }
  throw error;
}
*/

-- Assert constraints (PostgreSQL 11+)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  discounted_price DECIMAL(10,2),
  CHECK (discounted_price IS NULL OR discounted_price < price)
);

-- Temporal constraints
CREATE TABLE subscriptions (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  start_date DATE NOT NULL DEFAULT CURRENT_DATE,
  end_date DATE,
  CHECK (end_date IS NULL OR end_date > start_date),
  CHECK (start_date <= CURRENT_DATE + INTERVAL '1 year')
);

-- Cascading updates example
CREATE TABLE categories (
  code VARCHAR(10) PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_code VARCHAR(10),
  FOREIGN KEY (category_code) REFERENCES categories(code)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

-- Update category code cascades to products
UPDATE categories SET code = 'ELECTRONICS' WHERE code = 'ELEC';
-- Automatically updates products.category_code

-- MySQL constraint examples
/*
ALTER TABLE products
  ADD CONSTRAINT chk_price CHECK (price > 0);

ALTER TABLE orders
  ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;
*/

Constraints enforce data integrity at the database level. PRIMARY KEY ensures uniqueness and identifies rows. FOREIGN KEY maintains referential integrity. NOT NULL prevents null values. UNIQUE prevents duplicates. CHECK validates data conditions. DEFAULT provides fallback values. I use constraints to prevent invalid data—application bugs can't bypass database rules. Constraints document business rules. Understanding constraint violations guides error handling. Deferrable constraints postpone validation until transaction commit. Exclusion constraints prevent overlapping ranges. Domain constraints reuse validation logic. Constraints are defense against data corruption. Proper constraints reduce application validation code and ensure data quality.

Maria Garcia

More from Maria Garcia

Database constraints and data validation

0 Comments

More from Maria Garcia