init.sql - master - avrora1337/demo

init.sql
93 строки3 KB
avrora13377
first_commit
10 июн 2026, 06:35
10 июн 2026, 06:35c0c58f5
CREATE TABLE IF NOT EXISTS category (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS "pickup point" (
    id INTEGER NOT NULL PRIMARY KEY,
    postcode VARCHAR(100),
    city VARCHAR(100),
    street VARCHAR(100),
    building VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS "producer" (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS "provider" (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS "roles" (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS "status" (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS unit (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER NOT NULL,
    surname VARCHAR,
    name VARCHAR,
    middlename VARCHAR,
    login VARCHAR,
    password VARCHAR,
    user_id INTEGER,
    CONSTRAINT USERS_PK PRIMARY KEY (id),
    CONSTRAINT users_roles_FK FOREIGN KEY (user_id) REFERENCES roles(id)
);

CREATE TABLE IF NOT EXISTS product (
    id INTEGER NOT NULL,
    article VARCHAR,
    name VARCHAR,
    unit_id INTEGER,
    price MONEY,
    provider_id INTEGER,
    producer_id INTEGER,
    category_id INTEGER,
    discount NUMERIC,
    stock NUMERIC,
    description VARCHAR,
    photo VARCHAR,
    CONSTRAINT PRODUCT_PK PRIMARY KEY (id),
    CONSTRAINT product_unit_FK FOREIGN KEY (unit_id) REFERENCES unit(id),
    CONSTRAINT product_provider_FK FOREIGN KEY (id) REFERENCES provider(id),
    CONSTRAINT product_producer_FK FOREIGN KEY (id) REFERENCES producer(id),
    CONSTRAINT product_category_FK FOREIGN KEY (id) REFERENCES category(id)
);

CREATE TABLE IF NOT EXISTS product_in_order (
    id INTEGER NOT NULL,
    order_id INTEGER,
    product_id INTEGER,
    amount INTEGER,
    CONSTRAINT PRODUCT_IN_ORDER_PK PRIMARY KEY (id),
    CONSTRAINT product_in_order_product_FK FOREIGN KEY (product_id) REFERENCES product(id)
);

CREATE TABLE IF NOT EXISTS "order" (
    id INTEGER NOT NULL,
    order_date VARCHAR,
    delivery_date VARCHAR,
    pickup_point_id INTEGER,
    user_id INTEGER,
    delivery_code VARCHAR,
    status_id INTEGER,
    CONSTRAINT ORDER_PK PRIMARY KEY (id),
    CONSTRAINT order_pickup_point_FK FOREIGN KEY (pickup_point_id) REFERENCES "pickup point"(id),
    CONSTRAINT order_status_FK FOREIGN KEY (status_id) REFERENCES status(id),
    CONSTRAINT order_product_in_order_FK FOREIGN KEY (id) REFERENCES product_in_order(id),
    CONSTRAINT order_users_FK FOREIGN KEY (user_id) REFERENCES users(id)
);