How to enforce that a parent must have at least one child when the PK is composite?

Consider the following business domain:

• An Airline has a unique airline id (aid) and contains zero-or-more Planes.

• A Plane has a unique plane id (pid) in the Airline it flies for (but planes from different Airlines can have overlapping ids).

• Each Plane has one-or-more Seats.

• A Seat has a unique seat id (sid) in its plane (but Seats from different Planes may have overlapping ids).

My attempt so far

Here is my attempt at solving this:

CREATE SEQUENCE planes_seq;

CREATE TABLE Airlines (
    aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq')
);

CREATE TABLE Planes (
    aid INTEGER REFERENCES Airlines(aid)
,   pid INTEGER
,   PRIMARY KEY(aid, pid)
);

CREATE TABLE Seats (
    aid INTEGER
,   pid INTEGER
,   sid INTEGER
,   PRIMARY KEY(aid, pid, sid)
,   FOREIGN KEY(aid, pid) REFERENCES Planes(aid, pid)
);

ALTER TABLE Planes ADD CONSTRAINT fk_seats FOREIGN KEY(aid, pid)
REFERENCES Seats(aid, pid);

However, it fails because the final ALTER TABLE is illegal since the pair (Seats.aid, Seats.pid) is indeed not unique.
Questions

• How could I enforce the "a plane has at least one seat" constraint?

• Is this scheme in 3rd Normal Form?

PS: I am new to SQL and this is a (small part) of a homework assignment. I have tried following the parent-with-at-least-one-child example from another DBA question (Constraint to enforce "at least one" or "exactly one" in a database) but there are way too many tricks there that I cannot understand (multiple WITH queries, a RETURNING clause and so on). It looks to me like there must be a simpler way to do it.

The CTEs (WITH and RETURNING) are there to avoid the use of deferred constraints.

It is simpler conceptually to use deferred constraints and for a homework assignment, I suggest you start with using them. Example, with comments where it differs from yours:

CREATE SEQUENCE planes_seq;

CREATE TABLE airlines (
    aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq')
);

CREATE TABLE planes (
    aid INTEGER NOT NULL REFERENCES airlines (aid)
,   pid INTEGER NOT NULL 
,   sid INTEGER NOT NULL                -- we add a default seat
        DEFAULT 1 CHECK (sid = 1)       -- in every plane
,   PRIMARY KEY (aid, pid)
);

CREATE TABLE seats (
    aid INTEGER NOT NULL
,   pid INTEGER NOT NULL
,   sid INTEGER NOT NULL
,   PRIMARY KEY (aid, pid, sid)
,   FOREIGN KEY (aid, pid) REFERENCES planes (aid, pid) 
);

ALTER TABLE planes ADD CONSTRAINT fk_seats 
    FOREIGN KEY (aid, pid, sid) 
    REFERENCES seats (aid, pid, sid) 
    DEFERRABLE INITIALLY DEFERRED ;      -- the DEFERRABLE is important

Now we can add some data:

INSERT INTO airlines (aid) 
VALUES (1) ;                      -- our 1st airline

BEGIN ;
    INSERT INTO  planes      -- its 1st plane
        (aid, pid)           -- notice: sid is added by default as 1         
    VALUES 
        (1, 1) ;

    INSERT INTO seats 
        (aid, pid, sid) 
    VALUES                    -- 3 seats in the 1st plane
        (1, 1, 1), 
        (1, 1, 2), 
        (1, 1, 3) ;
COMMIT ;

We need to insert new planes (and their seats) inside a transaction, otherwise it would fail.

DEFERRABLE means that we can "defer" the check of the FOREIGN KEY constraint until the end of the transaction (not deferred constraints are checked immediately after insert/update/delete statements.) If you try to insert only in planes, it will fail.

INITIALLY DEFERRED means that we want to be "deferring" the foreign key constraint now, as soon as it is created. This could have been done later (you can set and unset constraints from deferred, if they have been defined as DEFERRABLE).

StackExchange Database Administrators Q#152734, answer score: 5