sql oracle - constraint on 2 columns from different tables

Personally, I would add an AIRCRAFT and a SEAT table:

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)
);

Then your flight table would reference the aircraft:

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
  -- ...
);

And the ticket would reference a flight and a seat:

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)
);

Then you can never sell a seat that does not exist on an aircraft and do not need to count the maximum number of tickets and compare it to seats (and the seat has added attributes like its location on the plane that can be displayed on the ticket).

All you need then is to ensure the referential consistency that, for a ticket, the flight and the seat are on the same aircraft; which can be done with a trigger:

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;
/

db<>fiddle here

You can use an AFTER STATEMENT trigger:

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;
/

It could be made more efficient by using a compound trigger to collate, for each row, the flight_id values into a collection and then, after the statement, only checking the number of tickets for those flights; however, I'll leave that extension as an exercise for the OP.

db<>fiddle here

As others indicated there is no :new.count column. This is because :new (and :old) create a pseudo-row containing exactly the same columns as the table definition. Further you will get a Mutating exception as what you need to count in the flight_number from tickets. However, since that is the table causing he trigger to fire you cannot reference it. So what to do: create a compound trigger, and a supporting Type (nested table). Within it use the after row section to capture the flight_numbers processed. Then in the after statement section you can select count of tickets for each flight. If that count > 0 then raise your exception. ( see Demo )

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; 

There remains a you need to handle: What happens if an update changes the flight number or perhaps (missing column) the data of the flight.