强制执行依赖于父列值的复合唯一约束

2021-09-14 00:00:00 sql constraints mysql unique-constraint database-normalization

使用提供的架构，我想以某种方式强制每个显示都有唯一的 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.

相关文章