Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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

CREATE TABLE



CREATE TABLE



ALTER TABLE


-- Proper way to insert meeting with 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;

INSERT 0 1


TABLE meeting;
TABLE session;


meeting_id
first_session_id
cost
startdate
enddate


1
1
1
2023-03-08
2023-03-09



SELECT 1




session_id
meeting_id
startdate
enddate


1
1
2023-03-08
2023-03-09



SELECT 1


-- session can't be hijacked by another meeting:
INSERT INTO meeting (first_session_id, cost, startdate   , enddate)
VALUES (1, 1   , '2023-03-08', '2023-03-09');

ERROR:  insert or update on table "meeting" violates foreign key constraint "meeting_first_session_id_fkey"
DETAIL:  Key (meeting_id, first_session_id)=(2, 1) is not present in table "session".


-- meeting can't be entered without session:
INSERT INTO meeting (cost, startdate   , enddate)
VALUES (1, '2023-03-08', '2023-03-09');

ERROR:  null value in column "first_session_id" of relation "meeting" violates not-null constraint
DETAIL:  Failing row contains (3, null, 1, 2023-03-08, 2023-03-09).