强制执行依赖于父列值的复合唯一约束 [英] Enforce composite unique constraint that depends on parent column value

问题描述

使用提供的架构，我想以某种方式强制每个显示都有唯一的 reserved_seat:seat_id.换句话说，如果该放映中已经预订了特定座位，则您无法预订该座位.

With provided schema i want to somehow enforce that there is unique reserved_seat:seat_id per showing. In other words you can't reserve specific seat if it is already reserved in that showing.

一种选择是同时将showing_id添加到reservation_seat(这是多余的)，然后对(showing_id,seat_id)进行唯一约束.

One option is to also add showing_id to reservation_seat (which is redundant) and then make unique constraint on (showing_id, seat_id).

这可以在 sql 中完成还是落在应用程序代码中?

Can this be done in sql or it falls to application code?

DDL:

CREATE TABLE showing
(
    id              INT  NOT NULL  AUTO_INCREMENT,
    name            VARCHAR(45) NOT NULL,
    PRIMARY KEY (id)
)

CREATE TABLE reservation
(
    id              INT  NOT NULL  AUTO_INCREMENT,
    showing_id      INT  NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (showing_id) REFERENCES showing(id)
)

CREATE TABLE reservation_seat
(
    id              INT  NOT NULL  AUTO_INCREMENT,
    reservation_id  INT  NOT NULL,
    seat_id         INT  NOT NULL,
    confirmed       TINYINT,
    PRIMARY KEY (id),
    FOREIGN KEY (reservation_id) REFERENCES reservation(id),
    FOREIGN KEY (seat_id) REFERENCES seat(id)
)

CREATE TABLE seat
(
    id              INT  NOT NULL  AUTO_INCREMENT,
    row             VARCHAR(45) NOT NULL,
    column          VARCHAR(45) NOT NULL,
    PRIMARY KEY (id)
)

推荐答案

我相信这是使用代理键(auto_increment id's)而不是自然键导致您误入歧途的罕见情况之一.考虑一下如果您使用自然键，您的表定义会是什么样子:

I believe that this is one of those rare cases where the use of surrogate keys (auto_increment id's) instead of natural keys has led you astray. Consider how your table definitions would look if you used natural keys instead:

CREATE TABLE showing
(
    name            VARCHAR(45) NOT NULL,   -- globally unique
    PRIMARY KEY (name)
)

CREATE TABLE reservation
(
    showing_name    VARCHAR(45) NOT NULL,
    name            VARCHAR(45) NOT NULL,   -- only unique within showing_name
    PRIMARY KEY (name, showing_name),
    FOREIGN KEY (showing_name) REFERENCES showing(name)
)

CREATE TABLE reservation_seat
(
    showing_name    VARCHAR(45) NOT NULL,
    reservation_name VARCHAR(45) NOT NULL,
    seat_row        VARCHAR(45) NOT NULL,
    seat_column     VARCHAR(45) NOT NULL,
    confirmed       TINYINT,
    PRIMARY KEY (showing_name, reservation_name, seat_row, seat_column),
    FOREIGN KEY (showing_name, reservation_name) REFERENCES reservation(showing_name, name),
    FOREIGN KEY (seat_row, seat_column) REFERENCES seat(row, column)
)

现在，您可以将每个显示约束的预留座位添加为reservation_seat 上的备用键:

Now you can add your reserved seat per showing constraint as an Alternate Key on reservation_seat:

CREATE TABLE reservation_seat
(
    showing_name    VARCHAR(45) NOT NULL,
    reservation_name VARCHAR(45) NOT NULL,
    seat_row        VARCHAR(45) NOT NULL,
    seat_column     VARCHAR(45) NOT NULL,
    confirmed       TINYINT,
    PRIMARY KEY (showing_name, reservation_name, seat_row, seat_column),
    FOREIGN KEY (showing_name, reservation_name) REFERENCES reservation(showing_name, name),
    FOREIGN KEY (seat_row, seat_column) REFERENCES seat(row, column),
    CONSTRAINT UC_seat_showing_reserved UNIQUE(showing_name, seat_row, seat_column)
)

然而，这清楚地表明主键是多余的，因为它只是我们添加的约束的较弱版本，所以我们应该用我们的新约束替换它.

However, this makes it clear that the primary key is superfluous because it's just a weaker version of the constraint that we have added, so we should replace it with our new constraint.

CREATE TABLE reservation_seat
(
    showing_name    VARCHAR(45) NOT NULL,
    reservation_name VARCHAR(45) NOT NULL,
    seat_row        VARCHAR(45) NOT NULL,
    seat_column     VARCHAR(45) NOT NULL,
    confirmed       TINYINT,
    PRIMARY KEY (showing_name, seat_row, seat_column),
    FOREIGN KEY (showing_name, reservation_name) REFERENCES reservation(showing_name, name),
    FOREIGN KEY (seat_row, seat_column) REFERENCES seat(row, column)
)

我们现在可能会担心，我们的reservation_seat 可能会引用与reservation_seat 本身不同的showing_id 的预订，但这对于自然键来说不是问题，因为第一个外键引用阻止了这种情况.

We may worry now that our reservation_seat could be referencing a reservation with a different showing_id than the reservation_seat itself, but that's not a problem for natural keys because the first foreign key reference prevents that.

现在我们需要做的就是将其转换回代理键:

Now all we need to do is to translate this back into surrogate keys:

CREATE TABLE reservation_seat
(
    id              INT  NOT NULL  AUTO_INCREMENT,
    showing_id      INT  NOT NULL,
    reservation_id  INT  NOT NULL,
    seat_id         INT  NOT NULL,
    confirmed       TINYINT,
    PRIMARY KEY (id),
    FOREIGN KEY (showing_id, reservation_id) REFERENCES reservation(showing_id, id),
    FOREIGN KEY (seat_id) REFERENCES seat(id),
    CONSTRAINT UC_seat_showing_reserved UNIQUE(showing_id, seat_id)
)

因为我们将reservation_seat(id) 作为主键，我们必须将命名的PK 定义改回唯一约束.与您的原始预订座位定义相比，我们最终添加了 Showing_id，但通过修改后的更强的第一个外键定义，我们现在确保预订座位在放映中是唯一的，并且预订座位不能具有与其父预订不同的显示 ID.

Because we're making the reservation_seat(id) the primary key, we have to change the named PK definition back into a unique constraint. Compared to your original reservation_seat definition, we end up with showing_id added, but with the modified stronger first foreign key definition we now insure both that reservation_seat are unique within a showing and that reservation_seat cannot have a showing_id different from its parent reservation.

(注意:您可能需要在上面的 SQL 代码中引用行"和列"列名称)

(Note: you will probably have to quote the 'row' and 'column' column names in the SQL code above)

附加说明: DBMS 对此有所不同(在这种情况下我不确定 MySql)，但许多会要求外键关系在目标上具有相应的主键或唯一约束(参考)表.这意味着您必须使用新的约束来更改 reservation 表，例如:

Additional Note: DBMS's vary on this (and I am not sure about MySql in this case), but many will require that a Foreign Key relation have a corresponding Primary Key or Unique Constraint on the target (referenced) table. This would mean that you would have to alter the reservation table with a new constraint like:

CONSTRAINT UC_showing_reserved UNIQUE(showing_id, id)

匹配我上面建议的 reservation_seat 上的新 FK 定义:

to match the new FK definition on reservation_seat that I suggested above:

FOREIGN KEY (showing_id, reservation_id) REFERENCES reservation(showing_id, id),

从技术上讲，这将是一个冗余约束，因为它是保留表上主键的较弱版本，但在这种情况下，SQL 可能仍需要它来实现 FK.

Technically, this would be a redundant constraint since it is a weaker version of the primary key on the reservation table, but in this case SQL would probably still require it to implement the FK.

这篇关于强制执行依赖于父列值的复合唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！