问sql oracle -对来自不同表的2列的约束

EN

CREATE TABLE aircraft (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT aircraft__id__pk PRIMARY KEY,
  tail_number  VARCHAR2(6)
               NOT NULL
               CONSTRAINT aircraft__tn__u UNIQUE
               CONSTRAINT aircraft__tn_chk CHECK(
                 REGEXP_LIKE(
                   tail_number,
                   '[A-Z]\d{1,5}|[A-Z]\d{1,4}[A-Z]|[A-Z]\d{1,3}[A-Z]{2}'
                 )
               ),
  manufacturer VARCHAR2(20)
               NOT NULL,
  model        VARCHAR2(20)
               NOT NULL,
  airline_id   CONSTRAINT aircraft__aid__fk REFERENCES airline(airline_id)
               NOT NULL
);

CREATE TABLE seat (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT seat__id__pk PRIMARY KEY,
  aircraft_id  CONSTRAINT seat__aid__fk REFERENCES aircraft(id)
               NOT NULL,
  seat_row     VARCHAR2(3)
               NOT NULL,
  seat_column  NUMBER
               NOT NULL,
  CONSTRAINT seat__aid_r_c__u UNIQUE (aircraft_id, seat_row, seat_column)
);

CREATE TABLE flight (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT flight__id__pk PRIMARY KEY,
  aircraft_id  CONSTRAINT flight__aid__fk REFERENCES aircraft(id)
               NOT NULL
  -- ...
);

CREATE TABLE ticket (
  id           NUMBER
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT ticket__id__pk PRIMARY KEY,
  flight_id    CONSTRAINT ticket__fid__fk REFERENCES flight(id)
               NOT NULL,
  seat_id      CONSTRAINT ticket__sid__fk REFERENCES seat(id)
               NOT NULL,
  -- ...
  CONSTRAINT ticket__fid_sid__u UNIQUE (flight_id, seat_id)
);

CREATE TRIGGER ticket_check_seat_on_flight
  BEFORE INSERT OR UPDATE ON ticket
  FOR EACH ROW
DECLARE
  is_valid NUMBER(1);
BEGIN
  SELECT 1
  INTO   is_valid
  FROM   flight f
         INNER JOIN seat s
         ON (f.aircraft_id = s.aircraft_id)
  WHERE  f.id = :NEW.flight_id
  AND    s.id = :NEW.seat_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(
      -20000,
      'Flight and seat are on different aircraft.'
    );
END;
/

CREATE TRIGGER ticket__check_number_of_seats
  AFTER INSERT OR UPDATE OR DELETE ON ticket
DECLARE
  is_invalid NUMBER(1,0);
BEGIN
  SELECT 1
  INTO   is_invalid
  FROM   flight f
         INNER JOIN (
           SELECT flight_id,
                  COUNT(*) AS tickets_sold
           FROM   ticket
           GROUP BY flight_id
         ) t
         ON f.id = t.flight_id
  WHERE  t.tickets_sold > f.number_of_seats;
  
  RAISE_APPLICATION_ERROR(
    -20000,
    'Too many tickets sold for flight.'
  );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

create type flight_tickets_ntt
         is table of integer;

create or replace trigger trg_ticket_ciu
   for update or insert on tickets
       compound trigger
       
    l_flights  flight_tickets_ntt := flight_tickets_ntt(); 
    
    after each row is 
    begin 
        if :new.flight_number not member of l_flights then 
            l_flights.extend ;
            l_flights(l_flights.count) := :new.flight_number; 
        end if; 
    end after each row; 
    
    after statement is
        l_flight_cnt flight.flight_number%type;
    begin 
         select count(*)
           into l_flight_cnt
           from flight f 
          where f.number_of_seats < 
                ( select count(*)
                    from tickets t 
                   where t.flight_number in 
                         ( select * 
                             from table (l_flights)
                         ) 
                );  
       
          if l_flight_cnt > 0 then 
             raise_application_error(-20000, 'Not enough seats');
          end if; 
           
    end after statement; 
end trg_ticket_ciu;