forked from jdaarevalo/docker_postgres_with_data
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcreate_tables.sql
More file actions
85 lines (77 loc) · 2.03 KB
/
Copy pathcreate_tables.sql
File metadata and controls
85 lines (77 loc) · 2.03 KB
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- Creation of product table
CREATE TABLE IF NOT EXISTS product (
product_id INT NOT NULL,
name varchar(250) NOT NULL,
PRIMARY KEY (product_id)
);
-- Creation of country table
CREATE TABLE IF NOT EXISTS country (
country_id INT NOT NULL,
country_name varchar(450) NOT NULL,
PRIMARY KEY (country_id)
);
-- Creation of city table
CREATE TABLE IF NOT EXISTS city (
city_id INT NOT NULL,
city_name varchar(450) NOT NULL,
country_id INT NOT NULL,
PRIMARY KEY (city_id),
CONSTRAINT fk_country
FOREIGN KEY(country_id)
REFERENCES country(country_id)
);
-- Creation of store table
CREATE TABLE IF NOT EXISTS store (
store_id INT NOT NULL,
name varchar(250) NOT NULL,
city_id INT NOT NULL,
PRIMARY KEY (store_id),
CONSTRAINT fk_city
FOREIGN KEY(city_id)
REFERENCES city(city_id)
);
-- Creation of user table
CREATE TABLE IF NOT EXISTS users (
user_id INT NOT NULL,
name varchar(250) NOT NULL,
PRIMARY KEY (user_id)
);
-- Creation of status_name table
CREATE TABLE IF NOT EXISTS status_name (
status_name_id INT NOT NULL,
status_name varchar(450) NOT NULL,
PRIMARY KEY (status_name_id)
);
-- Creation of sale table
CREATE TABLE IF NOT EXISTS sale (
sale_id varchar(200) NOT NULL,
amount DECIMAL(20,3) NOT NULL,
date_sale TIMESTAMP,
product_id INT NOT NULL,
user_id INT NOT NULL,
store_id INT NOT NULL,
PRIMARY KEY (sale_id),
CONSTRAINT fk_product
FOREIGN KEY(product_id)
REFERENCES product(product_id),
CONSTRAINT fk_user
FOREIGN KEY(user_id)
REFERENCES users(user_id),
CONSTRAINT fk_store
FOREIGN KEY(store_id)
REFERENCES store(store_id)
);
-- Creation of order_status table
CREATE TABLE IF NOT EXISTS order_status (
order_status_id varchar(200) NOT NULL,
update_at TIMESTAMP,
sale_id varchar(200) NOT NULL,
status_name_id INT NOT NULL,
PRIMARY KEY (order_status_id),
CONSTRAINT fk_sale
FOREIGN KEY(sale_id)
REFERENCES sale(sale_id),
CONSTRAINT fk_status_name
FOREIGN KEY(status_name_id)
REFERENCES status_name(status_name_id)
);