Working with Constraints

ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY (id);
-- Concurrently (large tables)
CREATE UNIQUE INDEX CONCURRENTLY customers_pkey ON customers(id);
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;

ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT orders_unique UNIQUE NULLS NOT DISTINCT (customer_id, code);

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
-- Safe pattern on large tables
ALTER TABLE customers ADD CONSTRAINT email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT email_nn;

ALTER TABLE orders
  ADD CONSTRAINT orders_total_pos CHECK (total_cents >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_pos;

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';
ALTER TABLE events ALTER COLUMN created_at SET DEFAULT now();

ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE products
  ADD CONSTRAINT products_price_nonneg CHECK (price >= 0);

CREATE TABLE products (
   id    bigserial PRIMARY KEY,
   sku   text UNIQUE NOT NULL,
   price numeric(12,2) CHECK (price >= 0)
);

CREATE TABLE bookings (
   user_id int,
   slot    tstzrange,
   PRIMARY KEY (user_id, slot),
   CONSTRAINT no_overlap EXCLUDE USING gist (user_id WITH =, slot WITH &&)
);

ALTER TABLE order_items
  ALTER CONSTRAINT order_items_order_fk DEFERRABLE INITIALLY DEFERRED;
BEGIN;
  SET CONSTRAINTS ALL DEFERRED;
  -- bulk swap parent/child rows
COMMIT;

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE meetings (
   room_id int NOT NULL,
   slot    tstzrange NOT NULL,
   EXCLUDE USING gist (room_id WITH =, slot WITH &&)
);

ALTER TABLE orders DROP CONSTRAINT orders_total_pos;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_total_pos;