WsLLNbWj

1create database if not exists `plant_service`;
2use plant_service;
3
4CREATE TABLE IF NOT EXISTS cities ( 
5id INT(11) AUTO_INCREMENT primary key,
6`name` varchar(30) NOT NULL,
7`country_name` varchar(80) NOT NULL
8);
9
10CREATE TABLE IF NOT EXISTS users ( 
11id INT(11) AUTO_INCREMENT primary key,
12`username` varchar(50) NOT NULL UNIQUE,
13`password` varchar(255) NOT NULL,
14`first_name` varchar(50) NOT NULL ,
15`last_name` varchar(50)   ,
16`age` INT(11) NOT NULL,
17`money` decimal(15,2) NOT NULL ,
18`city_id` INT(11)  NOT NULL,
19`register_date` DATE NOT NULL,
20CONSTRAINT fk_users_cities FOREIGN KEY(city_id) REFERENCES cities(id)
21);
22
23CREATE TABLE IF NOT EXISTS orders ( 
24id INT(11) AUTO_INCREMENT primary key,
25`user_id` INT(11)  NOT NULL,
26`order_date` DATE NOT NULL,
27`is_completed` TINYINT(1) DEFAULT FALSE, 
28CONSTRAINT fk_orders_users FOREIGN KEY(user_id) REFERENCES users(id)
29);
30
31CREATE TABLE IF NOT EXISTS plants ( 
32id INT(11) AUTO_INCREMENT primary key,
33`name` varchar(50) NOT NULL,
34`price` decimal(15,2) NOT NULL ,
35`color` varchar(50) ,
36`quantity` INT(11) DEFAULT 0
37);
38
39CREATE TABLE IF NOT EXISTS info_plants ( 
40`id` INT(11) AUTO_INCREMENT primary key,
41`plant_id` INT(11) NOT NULL UNIQUE,
42`family` varchar(50) NOT NULL,
43`genus`  varchar(40) NOT NULL,
44`purpose`  varchar(60),
45CONSTRAINT fk_info_plants_plants FOREIGN KEY(plant_id) REFERENCES plants(id)
46);
47
48CREATE TABLE IF NOT EXISTS plants_orders ( 
49`plant_id` INT(11)  NOT NULL ,
50`order_id` INT(11) NOT NULL ,
51CONSTRAINT pk_plants_orders PRIMARY KEY(plant_id,order_id),
52CONSTRAINT fk_plants_orders_plants FOREIGN KEY(plant_id) REFERENCES plants(id),
53CONSTRAINT fk_plants_orders_orders FOREIGN KEY(order_id) REFERENCES orders(id)
54);