Блог

Главная - Вопросы по программированию - PSQL обеспечивает уникальность, но в пределах подмножества таблицы, а не во всей системе

PSQL обеспечивает уникальность, но в пределах подмножества таблицы, а не во всей системе

#postgresql #database-design #schema #unique

#postgresql #база данных-дизайн #схема #уникальный

Вопрос:

Я создаю базу данных оценок для курсов с использованием PostgreSQL.

Я бы хотел, чтобы имена оценок были уникальными в рамках курса, но два курса могут иметь оценки с одинаковым именем.

 -- assessment contains the different assignments amp; 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. Если да, есть ли более идиоматичное решение, которое может включать изменения схемы?

Комментарии:

1. Если курс однозначно идентифицируется с помощью (course_code, year, period), то это должно быть уникальным ограничением.

2. assessment.course_id должен быть не a serial , а обычный integer столбец.

3. Это непонятно. Вы прокомментировали name в неправильной таблице? Вы говорите, что хотите определенную уникальность «имен оценки». Название в courses — это название курса или оценки? И является ли name в оценке названием оценки или курса? Значения имен курсов также определяют значения имен таблиц? Являются ли значения имен таблиц оценки также значениями имен курсов? Пожалуйста, четко опишите, что означает строка в таблице и каковы ограничения на возможные состояния базы данных. PS Пора прочитать учебник по информационному моделированию и проектированию баз данных, включая нормализацию до более высокого уровня NFs.

4. Ваша таблица «courses» на самом деле является таблицей «предложений» — где кто-то регистрируется, посещает и оценивает предложение курса — что должно быть подсказано тем, что course_code в ней не является PK / УНИКАЛЬНЫМ. Вы сбиваете с толку «курс». (Вероятно, ваш дизайн перегружает «name» не приводящим к путанице способом. Но мы не можем сказать, пока вы не дадите четкие значения таблиц и ограничения.)

5. Также, когда вы говорите «курс» в разделе «имена оценок должны быть уникальными в рамках курса, но два курса могут иметь оценки с одинаковыми именами», неясно, какую таблицу вы имеете в виду или имеете ли вы в виду уникальность в пределах идентификатора курса или course_code или даже некоторой пары (идентификатор оценки, курс) (в зависимости от того, какое из двух предыдущих значений слова «курс» применимо).

Ответ №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 amp;amp;);

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