Блог

У меня есть три таблицы

 aircrafts 
flights
events 
 

flight иметь внешний ключ aircraft_id

event также есть внешний ключ aircraft_id и дополнительный внешний ключ flight_id

Есть ли способ ограничить on events , чтобы гарантировать, что if flight_id не равно NULL, чем aircraft_id on event совпадает с aircraft_id on flight с этим flight_id ?

Это зависит от основного бизнес-правила. Так ли это, что «нет событий, требующих самолетов, которые также не нуждаются в полете». Если это так, то просто удалите aircraft_id из events. Однако это кажется маловероятным, т.Е. События обслуживания не требуют полетов. Поэтому переформулируйте правило как «событие требует либо полета, либо самолета». Сделайте aircraft_id и flight_id необязательными в событиях, затем создайте контрольное ограничение, которое требует, чтобы одно из них было null, а другое not null .

 create table events
       ( event_id integer generated always as identity  
       , aircraft_id  integer
       , flight_id integer
       , constraint events_pk primary key(event_id)
       , constraint event2aircraft_fk
                    foreign key (aircraft_id)
                    references aircraft(aircraft_id)
       , constraint event2flight_fk
                    foreign key (aircraft_id)
                    references aircraft(aircraft_id) 
       , constraint event_or_aircraft_ck
                    check (   (aircraft_id is null and flight_id is not null)  
                           or (aircraft_id is not null and flight_id is null)
                          )
       )
; 
 

Теперь, когда событие требует полета, идентификатор aircraft_id может быть получен только из flight.

Вам нужно добавить два ограничения

 ALTER table flights ADD constraint uniq_id_aircraft_id unique (id, aircraft_id);

ALTER table events ADD FOREIGN KEY (aircraft_id, flight_id) REFERENCES event_flights (aircraft_id, id);
 

чтобы получить то, что вы хотите