FreightTransport/create_tables.sql

-- Table: kotov_aa.cargo

-- DROP TABLE IF EXISTS kotov_aa.cargo;

CREATE TABLE IF NOT EXISTS kotov_aa.cargo
(
    cargoid integer NOT NULL DEFAULT nextval('cargo_cargoid_seq'::regclass),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    weight numeric(10,2) NOT NULL,
    CONSTRAINT cargo_pkey PRIMARY KEY (cargoid),
    CONSTRAINT cargo_weight_check CHECK (weight > 0::numeric)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.cargo
    OWNER to kotov_aa;
-- Table: kotov_aa.cities

-- DROP TABLE IF EXISTS kotov_aa.cities;

CREATE TABLE IF NOT EXISTS kotov_aa.cities
(
    cityid integer NOT NULL DEFAULT nextval('cities_cityid_seq'::regclass),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    region character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT cities_pkey PRIMARY KEY (cityid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.cities
    OWNER to kotov_aa;
-- Table: kotov_aa.customers

-- DROP TABLE IF EXISTS kotov_aa.customers;

CREATE TABLE IF NOT EXISTS kotov_aa.customers
(
    customerid integer NOT NULL DEFAULT nextval('customers_customerid_seq'::regclass),
    companyname character varying(100) COLLATE pg_catalog."default" NOT NULL,
    phone character varying(20) COLLATE pg_catalog."default",
    address text COLLATE pg_catalog."default",
    CONSTRAINT customers_pkey PRIMARY KEY (customerid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.customers
    OWNER to kotov_aa;
-- Table: kotov_aa.drivers

-- DROP TABLE IF EXISTS kotov_aa.drivers;

CREATE TABLE IF NOT EXISTS kotov_aa.drivers
(
    driverid integer NOT NULL DEFAULT nextval('drivers_driverid_seq'::regclass),
    lastname character varying(50) COLLATE pg_catalog."default" NOT NULL,
    fullname character varying(100) COLLATE pg_catalog."default" NOT NULL,
    tabnumber character varying(20) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT drivers_pkey PRIMARY KEY (driverid),
    CONSTRAINT drivers_tabnumber_key UNIQUE (tabnumber)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.drivers
    OWNER to kotov_aa;
-- Table: kotov_aa.orders

-- DROP TABLE IF EXISTS kotov_aa.orders;

CREATE TABLE IF NOT EXISTS kotov_aa.orders
(
    orderid integer NOT NULL DEFAULT nextval('orders_orderid_seq'::regclass),
    customerid integer NOT NULL,
    vehicleid integer NOT NULL,
    cargoid integer NOT NULL,
    routeid integer NOT NULL,
    orderdate date NOT NULL,
    plandate date NOT NULL,
    factdate date,
    status character varying(30) COLLATE pg_catalog."default" DEFAULT 'В пути'::character varying,
    CONSTRAINT orders_pkey PRIMARY KEY (orderid),
    CONSTRAINT orders_cargoid_fkey FOREIGN KEY (cargoid)
        REFERENCES kotov_aa.cargo (cargoid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT orders_customerid_fkey FOREIGN KEY (customerid)
        REFERENCES kotov_aa.customers (customerid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT orders_routeid_fkey FOREIGN KEY (routeid)
        REFERENCES kotov_aa.routes (routeid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT orders_vehicleid_fkey FOREIGN KEY (vehicleid)
        REFERENCES kotov_aa.vehicles (vehicleid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT orders_status_check CHECK (status::text = ANY (ARRAY['В пути'::character varying, 'Доставлен'::character varying, 'Отменён'::character varying]::text[]))
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.orders
    OWNER to kotov_aa;
-- Table: kotov_aa.routes

-- DROP TABLE IF EXISTS kotov_aa.routes;

CREATE TABLE IF NOT EXISTS kotov_aa.routes
(
    routeid integer NOT NULL DEFAULT nextval('routes_routeid_seq'::regclass),
    distance numeric(10,2) NOT NULL,
    fromcityid integer NOT NULL,
    tocityid integer NOT NULL,
    CONSTRAINT routes_pkey PRIMARY KEY (routeid),
    CONSTRAINT routes_fromcityid_fkey FOREIGN KEY (fromcityid)
        REFERENCES kotov_aa.cities (cityid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT routes_tocityid_fkey FOREIGN KEY (tocityid)
        REFERENCES kotov_aa.cities (cityid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT chk_cities_different CHECK (fromcityid <> tocityid),
    CONSTRAINT routes_distance_check CHECK (distance > 0::numeric)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.routes
    OWNER to kotov_aa;
-- Table: kotov_aa.users

-- DROP TABLE IF EXISTS kotov_aa.users;

CREATE TABLE IF NOT EXISTS kotov_aa.users
(
    id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
    username character varying(50) COLLATE pg_catalog."default" NOT NULL,
    password_hash character varying(255) COLLATE pg_catalog."default" NOT NULL,
    role character varying(20) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_username_key UNIQUE (username),
    CONSTRAINT users_role_check CHECK (role::text = ANY (ARRAY['admin'::character varying, 'dispatcher'::character varying, 'driver'::character varying]::text[]))
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.users
    OWNER to kotov_aa;
-- Table: kotov_aa.vehicles

-- DROP TABLE IF EXISTS kotov_aa.vehicles;

CREATE TABLE IF NOT EXISTS kotov_aa.vehicles
(
    vehicleid integer NOT NULL DEFAULT nextval('vehicles_vehicleid_seq'::regclass),
    brand character varying(50) COLLATE pg_catalog."default" NOT NULL,
    bodytype character varying(30) COLLATE pg_catalog."default",
    color character varying(30) COLLATE pg_catalog."default",
    driverid integer,
    CONSTRAINT vehicles_pkey PRIMARY KEY (vehicleid),
    CONSTRAINT vehicles_driverid_fkey FOREIGN KEY (driverid)
        REFERENCES kotov_aa.drivers (driverid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE SET NULL
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS kotov_aa.vehicles
    OWNER to kotov_aa;