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?
