SQLize | PHPize | SQLtest

1. Базовые справочники, от которых никто не зависит CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, phone VARCHAR(20), address TEXT ); CREATE TABLE statuses ( status_id SERIAL PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE delivery_zone ( zone_id SERIAL PRIMARY KEY, district VARCHAR(255), city VARCHAR(255), street VARCHAR(255) ); CREATE TABLE couriers ( courier_id SERIAL PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255), phone VARCHAR(50) ); CREATE TABLE reviews ( review_id SERIAL PRIMARY KEY, text VARCHAR(255), grade VARCHAR(50), customer_id INT NOT NULL, -- FK-ги можно сделать DEFERRABLE, чтобы потом избежать циклов FOREIGN KEY (customer_id) REFERENCES customers(customer_id) DEFERRABLE INITIALLY DEFERRED ); -- 2. «Заказы» пока без внешнего ключа на deliveries CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, creation_date TIMESTAMP, weight INT, size INT, customer_id INT, status_order_id INT, -- пока без FK на statuses_orders FOREIGN KEY (customer_id) REFERENCES customers(customer_id) DEFERRABLE INITIALLY DEFERRED ); -- 3. Статусы конкретных заказов CREATE TABLE statuses_orders ( status_order_id SERIAL PRIMARY KEY, ts TIMESTAMP, -- переименовал «date» → «ts», чтобы не путать с ключевым словом status_id INT, order_id INT, FOREIGN KEY (status_id) REFERENCES statuses(status_id) DEFERRABLE INITIALLY DEFERRED, FOREIGN KEY (order_id) REFERENCES orders(order_id) DEFERRABLE INITIALLY DEFERRED ); -- 4. Доставки (пока без FK на orders — иначе снова цикл) CREATE TABLE deliveries ( delivery_id SERIAL PRIMARY KEY, date_arrived TIMESTAMP, courier_id INT, zone_id INT, review_id INT, FOREIGN KEY (courier_id) REFERENCES couriers(courier_id) DEFERRABLE INITIALLY DEFERRED, FOREIGN KEY (zone_id) REFERENCES delivery_zone(zone_id) DEFERRABLE INITIALLY DEFERRED, FOREIGN KEY (review_id) REFERENCES reviews(review_id) DEFERRABLE INITIALLY DEFERRED ); -- 5. Теперь, когда обе стороны существуют, добавляем «обратные» ключи ALTER TABLE orders ADD COLUMN delivery_id INT, ADD CONSTRAINT fk_orders_delivery FOREIGN KEY (delivery_id) REFERENCES deliveries(delivery_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE orders ADD CONSTRAINT fk_orders_status_order FOREIGN KEY (status_order_id) REFERENCES statuses_orders(status_order_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE deliveries ADD CONSTRAINT fk_deliveries_order FOREIGN KEY (order_id) REFERENCES orders(order_id) DEFERRABLE INITIALLY DEFERRED;