


--- Define them

---   UNIQUE KEY title (title),

CREATE TABLE cd (
  id int(11) NOT NULL auto_increment,
  title varchar(255) NOT NULL default 'Untitled',
  PRIMARY KEY (id),	
  UNIQUE KEY (title)
) Type=InnoDB;

--- comment "syntax like this lets you add a comment to a column def",

CREATE TABLE song (
  id int(11) NOT NULL auto_increment,
  name varchar(255) NOT NULL default 'blank',
  PRIMARY KEY (id)
) Type=InnoDB;

CREATE TABLE track (
  id int(11) NOT NULL auto_increment,
  seqnum int(11) NOT NULL default '-1',	
  song int(11) NOT NULL default '0',
  cd int(11) NOT NULL default '0',
  PRIMARY KEY (id),
  INDEX (song),
  FOREIGN KEY (song) REFERENCES song(id) ON DELETE CASCADE,
  INDEX (cd),
  FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE
) Type=InnoDB;

---
--- variations:
---
---   id int(11) NOT NULL auto_increment PRIMARY KEY,
---   PRIMARY KEY (id),
---  
---   id int(11) NOT NULL auto_increment PRIMARY KEY,
---
--- from the documentation,
--- http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
---    * KEY is normally a synonym for INDEX.
---    * a UNIQUE index is one in which all values must be distinct.
---       (except that duplicate nulls are allowed)

---    

--- Populate them.

INSERT INTO cd VALUES (1,'Waltzing at the Grange');
INSERT INTO cd VALUES (2,'Celtic Groove Brand');

INSERT INTO song VALUES (1, 'The Forlorn Queen');
INSERT INTO song VALUES (2, 'A Random Waltz');
INSERT INTO song VALUES (3, 'A Random Groove');

--- Forlorn Queen (song 1) is on both CD's
INSERT INTO track VALUES (1, 10, 1, 1);
INSERT INTO track VALUES (2, 20, 1, 2);

--- Random Waltz is only on waltz album
INSERT INTO track VALUES (3, 30, 2, 1);

--- Random Groove is only on groove album.
INSERT INTO track VALUES (4, 30, 3, 2);
