/*
DROP TABLE autoren CASCADE;
DROP TABLE buecher CASCADE;
*/

/* Buecher-Tabelle */
CREATE TABLE buecher (
  id       INT AUTO_INCREMENT PRIMARY KEY, 
  titel    VARCHAR (255) NOT NULL, 
  verlag   VARCHAR (64), 
  jahr     DEC (4)
) ENGINE=INNODB;


/* Autoren-Tabelle */
CREATE TABLE autoren (
  id       INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  nname    VARCHAR (64) NOT NULL,
  vname    VARCHAR (64),
  position DEC (2) NOT NULL CHECK position > 0,
  buchid   INT NOT NULL,
  CONSTRAINT autorbuch FOREIGN KEY (buchid) REFERENCES buecher (id) ON DELETE CASCADE,
  UNIQUE (buchid, position)
) ENGINE=INNODB;
