2

ERD

How to implement this relationship in PostgreSQL?
I need every meeting to have at least one session, but I do not how to realize it.

This is my try:

CREATE TABLE Meeting(
    MeetingId INT PRIMARY KEY NOT NULL,
    Cost INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL
);

CREATE TABLE Session(
    SessId INT PRIMARY KEY NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    MeetingId INT NOT NULL REFERENCES Meeting(MeetingId) ON UPDATE CASCADE ON DELETE CASCADE
);

Should I add some new tables or constraints?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
朱哲昊
  • 31
  • 2
  • I think the foreign key should be in the other direction, from `meeting` to `session`. – Laurenz Albe Mar 08 '23 at 09:46
  • @LaurenzAlbe The description sounds like the OP wants to have multiple sessions per meeting, but guarantee that there is at least one. – Bergi Mar 08 '23 at 11:41
  • This requirement is hard to represent as a SQL constraint since you have a chicken-and-egg problem, see https://stackoverflow.com/q/10292355/1048572 or https://stackoverflow.com/q/7310121/1048572 – Bergi Mar 08 '23 at 11:48
  • @Bergi: The chicken-egg problem can be avoided. – Erwin Brandstetter Mar 08 '23 at 12:52
  • @ErwinBrandstetter I'd argue that you still have the chicken-and-egg problem between the two rows. Even if there there is a solution for it, it requires special precautions to solve it. – Bergi Mar 08 '23 at 13:21
  • @Bergi Fair enough. But since we have a simple solution, it's also fair to say we are avoiding the chicken-egg problem. – Erwin Brandstetter Mar 08 '23 at 13:50

1 Answers1

2

This may seem like a chicken-egg problem, but there is a (mostly) simple solution that works perfectly:

CREATE TABLE meeting (
  meeting_id       int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, first_session_id int  NOT NULL
, cost             int  NOT NULL
, startdate        date NOT NULL
, enddate          date NOT NULL
);

CREATE TABLE session (
  session_id       int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, meeting_id       int  NOT NULL
, startdate        date NOT NULL
, enddate          date NOT NULL
, CONSTRAINT session_meeting_id_fkey
  FOREIGN KEY (meeting_id) REFERENCES meeting ON UPDATE CASCADE ON DELETE CASCADE   
, UNIQUE (meeting_id, session_id)  -- needed for FK meeting_first_session_id_fkey
);

ALTER TABLE meeting ADD CONSTRAINT meeting_first_session_id_fkey
FOREIGN KEY (meeting_id, first_session_id) REFERENCES session(meeting_id, session_id);  -- no cascading

Now, a meeting cannot be inserted without also inserting a session. And referential integrity is enforced at all times.
To avoid another lurking chicken-egg problem, use a data-modifying CTE to insert a meeting and its first session:

WITH ins_meeting AS (
   INSERT INTO meeting
          (first_session_id                                        , cost, startdate   , enddate)
   VALUES (nextval(pg_get_serial_sequence('session', 'session_id')), 1   , '2023-03-08', '2023-03-09')
   RETURNING meeting_id, first_session_id
   )
INSERT INTO session (session_id, meeting_id, startdate, enddate)
SELECT first_session_id, meeting_id, '2023-03-08', '2023-03-09'
FROM   ins_meeting;

fiddle

This is optimized based on previous answers:

About the demonstrated IDENTITY columns:

Note the use of nextval(pg_get_serial_sequence('session', 'session_id')). See:

A PRIMARY KEY column is NOT NULL implicitly. Spelling that out is optional noise. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228