-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_entities.sql
59 lines (53 loc) · 1.15 KB
/
create_entities.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
DROP TABLE IF EXISTS Warehouse CASCADE;
DROP TABLE IF EXISTS Product_Inventory CASCADE;
DROP TABLE IF EXISTS Product CASCADE;
DROP TABLE IF EXISTS Category CASCADE;
-- tables
-- Table: Warehouse
CREATE TABLE Warehouse (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
address VARCHAR(255),
capacity INTEGER,
UNIQUE (name)
);
-- Table: Category
CREATE TABLE Category(
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
-- Insert: Category
INSERT INTO Category(name)
VALUES
('S'),
('M'),
('L'),
('XL');
-- Table: Product
CREATE TABLE Product(
id SERIAL PRIMARY KEY,
category_id INTEGER NOT NULL,
name VARCHAR(255),
price INTEGER,
CONSTRAINT fk_category
FOREIGN KEY(category_id)
REFERENCES Category(id)
ON DELETE SET NULL,
UNIQUE (name)
);
-- Table: Product_Inventory
CREATE TABLE Product_Inventory(
id SERIAL PRIMARY KEY,
warehouse_id INTEGER,
product_id INTEGER,
stock INTEGER,
CONSTRAINT fk_warehouse
FOREIGN KEY(warehouse_id)
REFERENCES Warehouse(id)
ON DELETE CASCADE,
CONSTRAINT fk_product
FOREIGN KEY(product_id)
REFERENCES Product(id)
ON DELETE CASCADE,
UNIQUE (warehouse_id, product_id)
);