mzVMJRS3

1CREATE  TABLE IF NOT EXISTS `joe`.`products_to_categories` (
2  `product_to_category_id` INT NOT NULL AUTO_INCREMENT ,
3  `category_id` INT NOT NULL ,
4  `product_id` INT NOT NULL ,
5  PRIMARY KEY (`product_to_category_id`) ,
6  INDEX `category_id` (`category_id` ASC) ,
7  INDEX `product_id` (`product_id` ASC) ,
8  CONSTRAINT `category_id`
9    FOREIGN KEY (`category_id` )
10    REFERENCES `joe`.`categories` (`category_id` )
11    ON DELETE CASCADE
12    ON UPDATE NO ACTION,
13  CONSTRAINT `product_id`
14    FOREIGN KEY (`product_id` )
15    REFERENCES `joe`.`products` (`product_id` )
16    ON DELETE CASCADE
17    ON UPDATE NO ACTION)
18ENGINE = InnoDB;
19	
20INDEX `product_id_fkey` (`product_id` ASC) ,
21	
22use test;
23
24create table if not exists test.product
25(
26    product_id int not null auto_increment,
27    name varchar(80) not null,
28    primary key(product_id)
29);
30
31create table if not exists test.category
32(
33    category_id int not null auto_increment,
34    name varchar(80) not null,
35    primary key(category_id)
36);
37
38create table if not exists test.product_category
39(
40    product_id int,
41    category_id int,
42    primary key(product_id, category_id),
43    constraint product_id_fkey
44        foreign key(product_id) references product(product_id)
45        on delete cascade
46        on update no action,
47    constraint category_id_fkey
48        foreign key(category_id) references category(category_id)
49        on delete cascade
50        on update no action
51);
52
53insert into test.product(name) values('teddy bear');
54insert into test.category(name) values('toy');
55insert into test.product_category 
56    select p.product_id, c.category_id from product as p, category as c
57    where p.name = 'teddy bear' and c.name = 'toy';