postgresql - PSQL 强制唯一性，但在表的子集内，而不是通过整个事物

Question

我正在为使用 PostgreSQL 的课程创建评估数据库。

我希望评估名称在课程中是唯一的，但两门课程可以有相同名称的评估。

-- assessment contains the different assignments & labs that
-- students may submit their code to.
CREATE TABLE assessment (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(255)    UNIQUE NOT NULL,
    comments     TEXT            NOT NULL,
    type         ASSESSMENT_TYPE NOT NULL,
    course_id    SERIAL          NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- courses contains the information about a course. Since
-- the same course can run multiple times, a single course
-- is uniquely identified by (course_code, year, period)
CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) UNIQUE NOT NULL, -- Unique within all courses. Wrong!
    course_code VARCHAR(20)  NOT NULL,
    period      PERIOD       NOT NULL,
    year        INTEGER      NOT NULL
);

两个要点：

1. 我可以在不更改架构的情况下执行此操作吗？

2. 如果是这样，是否有可能包括架构更改的更惯用的解决方案？

标签： postgresqldatabase-designschemaunique

Answer 1

1. 我可以在不改变架构的情况下做到这一点吗？

不，因为您在这里有多个问题。

1. 您的评估在名称上是全球唯一的，而不是在课程中。
2. assessment.course_id有自己的无用序列（SERIAL只是INTEGER+ SEQUENCE）
3. 表courses定义了一个不存在的列数据类型：（PERIOD至少到版本 11 不）

2. 如果是这样，是否有可能包括架构更改的更惯用的解决方案？

应该执行您想要的修改的架构如下所示：

CREATE TABLE courses (
    id          SERIAL       PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    course_code VARCHAR(20)  NOT NULL,
    period      tstzrange    NOT NULL
);

-- the following is required to build the proper unique constraint...
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- the unique constraint: no two courses with same name at any point in time
ALTER TABLE courses
    ADD CONSTRAINT idx_unique_courses
    EXCLUDE USING GIST (name WITH =, period WITH &&);

CREATE TABLE assessment (
    id           SERIAL          PRIMARY KEY,
    name         VARCHAR(255)    NOT NULL,
    comments     TEXT            NOT NULL,
    type         ASSESSMENT_TYPE NOT NULL,
    course_id    INTEGER         NOT NULL REFERENCES courses(id),
    UNIQUE (course_id, name)
);