2
-- Host: localhost Database: test
3
-- ------------------------------------------------------
4
-- Server version 5.1.53-log
6
DROP TABLE IF EXISTS `denorm_items`;
7
CREATE TABLE `denorm_items` (
8
`id` int(11) NOT NULL AUTO_INCREMENT,
9
`type` varchar(16) DEFAULT NULL,
10
`color` varchar(16) DEFAULT NULL,
12
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
14
LOCK TABLES `denorm_items` WRITE;
15
INSERT INTO `denorm_items` VALUES (1,'t1','red'),(2,'t2','red'),(3,'t2','blue'),(4,'t3','black'),(5,'t4','orange'),(6,'t5','green');
18
DROP TABLE IF EXISTS `types`;
19
CREATE TABLE `types` (
20
`type_id` int(11) NOT NULL AUTO_INCREMENT,
21
`type` varchar(16) DEFAULT NULL,
22
PRIMARY KEY (`type_id`)
23
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
25
DROP TABLE IF EXISTS `colors`;
26
CREATE TABLE `colors` (
27
`color_id` int(11) NOT NULL AUTO_INCREMENT,
28
`color` varchar(16) DEFAULT NULL,
29
PRIMARY KEY (`color_id`)
30
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
32
DROP TABLE IF EXISTS `items`;
33
CREATE TABLE `items` (
34
`item_id` int(11) NOT NULL AUTO_INCREMENT,
35
`type_id` int(11) NOT NULL,
36
`color_id` int(11) NOT NULL,
37
PRIMARY KEY (`item_id`),
38
KEY `type_id` (`type_id`),
39
KEY `color_id` (`color_id`),
40
CONSTRAINT `items_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `types` (`type_id`) ON UPDATE CASCADE,
41
CONSTRAINT `items_ibfk_2` FOREIGN KEY (`color_id`) REFERENCES `colors` (`color_id`) ON UPDATE CASCADE
42
) ENGINE=InnoDB DEFAULT CHARSET=latin1;