gfY4pwCC

1drop table if exists category_size;
2drop table if exists category_material;
3drop table if exists item;
4drop type if exists size_enum;
5drop type if exists material_enum;
6drop type if exists category_enum;
7
8create type size_enum as enum ('S', 'M', 'L', '5', '6', '7');
9create type material_enum as enum ('LEATHER', 'CUTTON');
10create type category_enum as enum ('TSHIRT', 'SHOES');
11
12create table category_size(
13  category category_enum not null,
14  size size_enum not null,
15  primary key(category, size)
16);
17
18create table category_material(
19  category category_enum not null,
20  material material_enum not null,
21  primary key(category, material)
22);
23
24create table item(
25  id int not null primary key,
26  category category_enum not null,
27  size size_enum not null,
28  material material_enum not null,
29  constraint fk_category_size foreign key (category, size) references category_size(category, size),
30  constraint fk_category_material foreign key (category, material) references category_material(category, material)
31);
32
33insert into category_size values ('TSHIRT', 'S');
34insert into category_size values ('TSHIRT', 'M');
35insert into category_size values ('TSHIRT', 'L');
36insert into category_size values ('SHOES', '5');
37insert into category_size values ('SHOES', '6');
38insert into category_size values ('SHOES', '7');
39insert into category_material values ('TSHIRT', 'CUTTON');
40insert into category_material values ('SHOES', 'LEATHER');
41
42-- valid operations
43insert into item values (1, 'TSHIRT', 'S', 'CUTTON');
44insert into item values (2, 'TSHIRT', 'M', 'CUTTON');
45insert into item values (3, 'TSHIRT', 'L', 'CUTTON');
46insert into item values (4, 'SHOES', '5', 'LEATHER');
47insert into item values (5, 'SHOES', '6', 'LEATHER');
48insert into item values (6, 'SHOES', '7', 'LEATHER');
49
50-- invalid operations
51-- insert into item values (7, 'TSHIRT', 'S', 'LEATHER');
52-- insert into item values (8, 'SHOES', 'S', 'LEATHER');