Meetings - ryanseaton/psql-workshop GitHub Wiki

CREATE TABLE meeting(
 id serial PRIMARY KEY,
 name VARCHAR (50) UNIQUE NOT NULL,
 start_time timestamp without time zone,
 duration INTEGER NOT NULL
);

CREATE TABLE meeting_room(
 id serial PRIMARY KEY,
 name VARCHAR (50) UNIQUE NOT NULL
);

CREATE TABLE person(
 id serial PRIMARY KEY,
 name VARCHAR (50) UNIQUE NOT NULL
);

ALTER TABLE meeting
  ADD COLUMN owner integer,
  ADD FOREIGN KEY ("owner") REFERENCES "public"."person"("id");

INSERT INTO "public"."meeting"("id", "name", "start_time", "duration", "owner") VALUES(2, 'other meeting', 'now()', 15, 1) RETURNING "id", "name", "start_time", "duration", "owner";
INSERT INTO "public"."meeting"("id", "name", "start_time", "duration", "owner") VALUES(3, '3rd meeting', 'now()', 60, 1) RETURNING "id", "name", "start_time", "duration", "owner";

CREATE TABLE meeting_participants(
 meeting_id integer NOT NULL,
 person_id integer NOT NULL,
 PRIMARY KEY (meeting_id, person_id),
 CONSTRAINT meeting_id_fkey FOREIGN KEY (meeting_id)
      REFERENCES meeting (id)
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT person_id_fkey FOREIGN KEY (person_id)
      REFERENCES person (id)
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO "public"."meeting_participants"("meeting_id", "person_id") VALUES(3, 2) RETURNING "meeting_id", "person_id";