tdd001.sql

CREATE TABLE `phonetags` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `phonetag` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `mailtags` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mailtag` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `contacts` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  `surname` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `mails` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mail` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  `contact_id` INT(11) UNSIGNED NOT NULL,
  `mailtag_id` INT(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_MAILS_CONTACT_ID` (`contact_id`),
  KEY `FK_MAILS_MAILTAG_ID` (`mailtag_id`),
  CONSTRAINT `FK_MAILS_MAILTAG_ID` FOREIGN KEY (`mailtag_id`) REFERENCES `mailtags` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_MAILS_CONTACT_ID` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `phones` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  `contact_id` INT(11) UNSIGNED NOT NULL,
  `phonetag_id` INT(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_PHONES_CONTACT_ID` (`contact_id`),
  KEY `FK_PHONES_PHONETAG_ID` (`phonetag_id`),
  CONSTRAINT `FK_PHONES_CONTACT_ID` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_PHONES_PHONETAG_ID` FOREIGN KEY (`phonetag_id`) REFERENCES `phonetags` (`id`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;