See this sample schema

Passenger(id PK, Name)

Plane(id  PK, capacity, type);

Flight(id PK, planeId FK(Plane), flightDate, StartLocation, destination)


CREATE TABLE Reservation(PassengerId, flightId,
        PRIMARY KEY (passengerId, flightId),
        FOREIGN KEY (passengerId) REFERENCES Passenger,
        FOREIGN KEY (flightId) REFERENCES Flight);

I need to define an integrity constraint that enforces the restriction that the number of passengers on a plane cannot exceed the plane’s capacity.

I have tried and achieved so far is this.

CREATE TABLE Reservation(
    passengerId INTEGER,  
    flightId    INTEGER,
        PRIMARY KEY (passengerId, flightId),
        FOREIGN KEY (passengerId) REFERENCES Passenger,
        FOREIGN KEY (flightId) REFERENCES Flight,
        Constraint check1 
            check(Not Exists(select * from Flight s, (select count(*) as totalRes from Reservation group by flightId) t
where t.totalRes  > s.capacity ) )            
 ); 

I am not sure i am doing in right way or not.

Any suggestions?