问题

[![ERD][1]][1]

如何在PostgreSQL中实现这种关系？
我需要确保每个会议至少有一个会话，但我不知道如何实现它。

这是我的尝试：

```sql
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
);

我应该添加一些新表或约束吗？

<details>
<summary>英文:</summary>

[![ERD][1]][1]

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:

```sql
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?

答案1

得分: 3

以下是翻译好的内容：

这似乎是一个鸡生蛋的问题，但有一个（大部分）简单的解决方案，可以完美解决：

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

现在，不可以插入会议而不插入会话。并且一直强制执行引用完整性。为了避免另一个潜在的鸡生蛋问题，使用数据修改的 CTE 来插入会议 和 其第一个会话：

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

这是基于以前的答案进行优化的：

• https://stackoverflow.com/questions/24813000/how-to-deal-with-mutually-dependent-inserts/24816197#24816197
• https://stackoverflow.com/questions/68244745/chicken-or-egg-problem-in-table-schema-postgresql/68245259#68245259

关于演示的 IDENTITY 列：

• https://stackoverflow.com/questions/9875223/auto-increment-table-column/9875517#9875517

请注意使用 nextval(pg_get_serial_sequence('session', 'session_id'))。请参考：

• https://stackoverflow.com/questions/12433075/reference-value-of-serial-column-in-another-column-during-same-insert/12433446#12433446

PRIMARY KEY 列在隐式情况下是 NOT NULL。拼写出来是可选的噪音。请参考：

• https://stackoverflow.com/questions/20006374/why-can-i-create-a-table-with-primary-key-on-a-nullable-column/20006502#20006502
• https://stackoverflow.com/questions/26642835/composite-primary-key-enforces-not-null-constraints-on-involved-columns/26643137#26643137

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

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;