問題描述

使用提供的架構，我想以某種方式強制每個顯示都有唯一的 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.

這篇關于強制執行依賴于父列值的復合唯一約束的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！