Constraints and Schema

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE orders
  ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users (id)
  ON DELETE RESTRICT;

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

ALTER TABLE users
  ADD COLUMN locale text NOT NULL DEFAULT 'en';

ALTER TABLE enrollments
  ADD CONSTRAINT enrollments_uniq UNIQUE (course_id, user_id);

-- ALTER TABLE rooms ADD EXCLUDE USING gist (room_id WITH =, during WITH &&);

ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

ALTER TABLE users RENAME COLUMN full_name TO name;
ALTER TABLE users RENAME TO app_users;

ALTER TABLE products DROP CONSTRAINT products_price_pos;

ALTER TABLE t
  ADD COLUMN full_name text
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

COMMENT ON COLUMN users.email IS 'Login identity';

CREATE SCHEMA app;
SET search_path TO app, public;
-- app.users resolves first

INSERT INTO users (email) VALUES ('a@b.co') RETURNING id;
-- id from sequence

DROP TABLE old_events CASCADE;
-- drops FKs/views depending on it