
// CF 5.4

// Incumbents table & data

CREATE TABLE Incumbents (
                        SSN             integer,
                        PCN             integer,
                        StartDate       date,
                        EndDate         date
                        );

// No assertions; triggers used instead

CREATE TRIGGER seq_primary_key
BEFORE COMMIT
IF (EXISTS (SELECT I1.SSN
              FROM Incumbents AS I1
              WHERE 1 < (SELECT COUNT(SSN)
                 FROM Incumbents AS I2
                 WHERE I1.SSN = I2.SSN AND I1.PCN = I2.PCN
                    AND I1.StartDate < I2.EndDate
                    AND I2.StartDate < I1.EndDate))
    OR EXISTS (SELECT *
                FROM Incumbents AS I
                WHERE I.SSN IS NULL OR I.PCN IS NULL)
)
EXECUTE REJECT;

