pm04/database.sql

create table Driver (
	driver_id serial primary key,
	name varchar(64) not null,
	surname varchar(64) not null,
	patronimic varchar(64) not null
);

create table Transport (
	transport_id serial primary key,
	branch varchar(64) not null,
	model varchar(64) not null,
	license_plate_number varchar(64) not null,
	driver_id int
);

create table Transport_Flight (
	flight_id serial primary key,
	duration time,
	transport_id int,
	route_id int
);

create table Route (
	route_id serial primary key,
	route_name text not null
);

create table Route_Point (
	route_point_id serial primary key,
	route_id int,
	point_id int,
	order_of_the_route int not null
);

create table Point (
	point_id serial primary key,
	point_name text not null
);

create table Users(
	user_id serial primary key,
	login varchar(24) not null unique,
	password text not n
	role_id int
)

alter table Transport add foreign key (driver_id) references Driver(driver_id);

alter table Transport_Flight add foreign key (transport_id) references Transport(transport_id);
alter table Transport_Flight add foreign key (route_id) references Route(route_id);

alter table Route_Point add foreign key (route_id) references Route(route_id);
alter table Route_Point add foreign key (point_id) references Point(point_id);

--триггер на валидацию ввода длительности поездки
create or replace function check_flight_duration()
returns trigger as $$
begin
    if new.duration <= time '00:00:00' then
        raise exception 'Длительность рейса должна быть больше 00:00:00';
    end if;

    return new;
end;
$$ language plpgsql;

create trigger trg_check_flight_duration
before insert or update
on transport_flight
for each row
execute function check_flight_duration();

select * from transport
update transport set driver_id = null where transport_id = 1
select * from driver