格斗赛事关系型数据库建模：多实体关联与SQL错误排查

1. 清理冲突表（可选，无需保留数据时使用） #

数据库中已存在同名表导致创建报错，先按依赖顺序删除：

DROP TABLE IF EXISTS Contest, Participant, Judge, Referee;

2. 修复表结构与创建顺序 #

原SQL错误在于先创建了依赖其他表的Contest，且现有Contest表缺失关联字段。正确顺序是先建被引用的基础表，再建依赖表：

-- 1. 创建选手表（被赛事表引用）
CREATE TABLE "Participant" (
  "participant_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "participant_wins" INT,
  "participant_losses" INT,
  "participant_draws" INT
);

-- 2. 创建普通裁判表
CREATE TABLE "Judge" (
  "judge_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "judge_name" VARCHAR(100) NOT NULL
);

-- 3. 创建主裁表
CREATE TABLE "Referee" (
  "referee_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "referee_name" VARCHAR(100) NOT NULL
);

-- 4. 创建赛事表，直接定义外键（此时基础表已存在）
CREATE TABLE "Contest" (
  "contest_id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "contest_date" DATE NOT NULL,
  "contest_location" VARCHAR(200),
  "referee_id" INT REFERENCES "Referee"("referee_id"),
  "participant_1_id" INT REFERENCES "Participant"("participant_id"),
  "participant_2_id" INT REFERENCES "Participant"("participant_id")
);

如果需要保留现有表并补全字段，执行：

-- 给Contest表添加缺失的选手字段
ALTER TABLE "Contest" ADD COLUMN "participant_1_id" INT;
ALTER TABLE "Contest" ADD COLUMN "participant_2_id" INT;

-- 添加外键约束
ALTER TABLE "Contest" ADD FOREIGN KEY ("participant_1_id") REFERENCES "Participant"("participant_id");
ALTER TABLE "Contest" ADD FOREIGN KEY ("participant_2_id") REFERENCES "Participant"("participant_id");

原设计用固定字段存储选手、裁判的方式扩展性差，建议用中间关联表实现标准多对多关系：

1. 选手与赛事的多对多关联 #

创建Contest_Participants中间表，支持选手参与多场赛事，同时扩展比赛角色/结果：

CREATE TABLE "Contest_Participants" (
  "contest_id" INT REFERENCES "Contest"("contest_id"),
  "participant_id" INT REFERENCES "Participant"("participant_id"),
  "fight_role" VARCHAR(20) NOT NULL CHECK ("fight_role" IN ('red', 'blue')),
  "result" VARCHAR(10) CHECK ("result" IN ('win', 'loss', 'draw')),
  PRIMARY KEY ("contest_id", "participant_id") -- 避免重复绑定
);

此时可删除Contest表中冗余的选手字段：

ALTER TABLE "Contest" DROP COLUMN "participant_1_id";
ALTER TABLE "Contest" DROP COLUMN "participant_2_id";

2. 裁判与赛事的多对多关联 #

创建Contest_Judges中间表，支持一名裁判执裁多场赛事，灵活控制每场赛事的裁判数量：

CREATE TABLE "Contest_Judges" (
  "contest_id" INT REFERENCES "Contest"("contest_id"),
  "judge_id" INT REFERENCES "Judge"("judge_id"),
  PRIMARY KEY ("contest_id", "judge_id")
);

3. 主裁与赛事的关联 #

主裁与赛事是一对多关系（一场赛事对应一名主裁，主裁可执裁多场），直接在Contest表中保留referee_id外键即可，无需额外中间表。

Table Participant {
    participant_id [pk, increment]
    participant_wins int
    participant_losses int
    participant_draws int
}

Table Judge {
    judge_id [pk, increment]
    judge_name varchar(100) [not null]
}

Table Referee {
    referee_id [pk, increment]
    referee_name varchar(100) [not null]
}

Table Contest {
    contest_id [pk, increment]
    contest_date date [not null]
    contest_location varchar(200)
    referee_id int [ref: > Referee.referee_id]
}

Table Contest_Participants {
    contest_id int [ref: > Contest.contest_id]
    participant_id int [ref: > Participant.participant_id]
    fight_role varchar(20) [not null, check: fight_role in ('red', 'blue')]
    result varchar(10) [check: result in ('win', 'loss', 'draw')]
    pk {contest_id, participant_id}
}

Table Contest_Judges {
    contest_id int [ref: > Contest.contest_id]
    judge_id int [ref: > Judge.judge_id]
    pk {contest_id, judge_id}
}

• 执行SQL时严格遵循表的依赖顺序：先建基础表，再建赛事表，最后建中间关联表。
• 若需保留现有数据，操作前务必备份，避免数据丢失。
• 可根据业务需求给外键添加ON DELETE CASCADE或ON DELETE SET NULL规则，控制关联数据的删除行为。

内容的提问来源于stack exchange，提问作者lucasCage