/***********************************************************************
 * http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html
 * http://www.faqs.org/docs/ppbook/x13546.htm
 ***********************************************************************/

DROP TRIGGER IF EXISTS update_hobby ON hobby;

DROP TABLE IF EXISTS hobbies;
DROP TABLE IF EXISTS hobby;
DROP TABLE IF EXISTS person;

CREATE TABLE person
(
 pid  INTEGER NOT NULL,
 name VARCHAR(50) NOT NULL,
 PRIMARY KEY (pid)
);

CREATE TABLE hobby
(
 name VARCHAR(50) NOT NULL,
 PRIMARY KEY (name)
);

CREATE TABLE hobbies
(
 pid INTEGER NOT NULL,
 hid VARCHAR(50) NOT NULL,
 PRIMARY KEY (pid,hid),
 FOREIGN KEY (pid) REFERENCES person (pid)
   ON DELETE CASCADE ON UPDATE CASCADE
   DEFERRABLE INITIALLY DEFERRED,
 FOREIGN KEY (hid) REFERENCES hobby (name)
   ON DELETE CASCADE ON UPDATE CASCADE
   DEFERRABLE INITIALLY DEFERRED
);


INSERT INTO person(pid, name) VALUES
  (1,  'Wolfgang'),
  (2,  'Marianne'),
  (3,  'Sibylle'),
  (4,  'Sonja'),
  (5,  'Verena');

INSERT INTO hobby(name) VALUES
  ('Akkordeon'),
  ('Flöte'),
  ('Gitarre');

INSERT INTO hobbies(pid, hid) VALUES
  (1, 'Akkordeon'),
  (1, 'Flöte'),
  (2, 'Akkordeon'),
  (3, 'Akkordeon'),
  (3, 'Gitarre'),
  (4, 'Flöte'),
  (5, 'Akkordeon');