ch11-create.sql

drop schema if exists playground cascade;

create schema playground;

create table playground.users (
  email varchar(100) check (length(email) > 5 and position('@' in email) > 0),
  first_name varchar(100),
  last_name varchar(100),
  is_active boolean not null default true,
  constraint pk_users primary key (email)
);

create table playground.notes (
  note_id bigint generated by default as identity primary key,
  title text not null unique,
  note text not null,
  user_email varchar(100) references playground.users (email)
    on update cascade
    on delete cascade,
  create_ts timestamptz not null default now(),
  edit_ts timestamptz not null default now (),
  check (edit_ts >= create_ts)
);

create table playground.note_tags (
  note_id bigint,
  tag varchar(20) not null check (tag in ('work', 'personal', 'todo')),
  primary key (note_id, tag),
  foreign key (note_id) references playground.notes (note_id)
    on update cascade
    on delete cascade
);