Enforce Composite Unique Constraint That Depends On Parent Column Value

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 t

Solution 1:

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:

CREATETABLE showing
(
    name            VARCHAR(45) NOTNULL,   -- globally uniquePRIMARY KEY (name)
)

CREATETABLE reservation
(
    showing_name    VARCHAR(45) NOTNULL,
    name            VARCHAR(45) NOTNULL,   -- only unique within showing_namePRIMARY KEY (name, showing_name),
    FOREIGN KEY (showing_name) REFERENCES showing(name)
)

CREATETABLE reservation_seat
(
    showing_name    VARCHAR(45) NOTNULL,
    reservation_name VARCHAR(45) NOTNULL,
    seat_row        VARCHAR(45) NOTNULL,
    seat_column     VARCHAR(45) NOTNULL,
    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)
)

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

CREATETABLE reservation_seat
(
    showing_name    VARCHAR(45) NOTNULL,
    reservation_name VARCHAR(45) NOTNULL,
    seat_row        VARCHAR(45) NOTNULL,
    seat_column     VARCHAR(45) NOTNULL,
    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.

CREATETABLE reservation_seat
(
    showing_name    VARCHAR(45) NOTNULL,
    reservation_name VARCHAR(45) NOTNULL,
    seat_row        VARCHAR(45) NOTNULL,
    seat_column     VARCHAR(45) NOTNULL,
    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)
)

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:

CREATETABLE reservation_seat
(
    id              INTNOTNULL  AUTO_INCREMENT,
    showing_id      INTNOTNULL,
    reservation_id  INTNOTNULL,
    seat_id         INTNOTNULL,
    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)
)

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.

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

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)

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

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

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.

Solution 2:

Does it take 90 characters to specify a "seat"? The seats I am familiar with are like "103-45" or "J17". Or even "Sec 4 Row 43 Seat 105". You have not mentioned it, but row/column is not adequate to answer the question "are these two seats adjacent?"

My first approach to the problem is to get rid of the table seat, other than being able to enumerate all the seats in a venue.

Then I would question the table reservation_seat, which smells like a many-to-many mapping (plus a flag). Many:many implies non uniqueness. So, something has to give.

The raw, unnormalized, data seems to be

showing:  showing_id (PK), date, time, location
reservation:  showing_id, seat, confirmed

Having this (on reservation) probably answers your question:

PRIMARY KEY(showing_id, seat)

It ties the two tables together, provides a 'natural' PK, and still allows for the confirmed flag.

I don't know your logic for "confirming". I assume that you cannot reassign a seat while it is waiting to be confirmed?

Back to my starting comment. seat VARCHAR(15) might be appropriate. And, if you need it, another table could have

CREATETABLE venue (
    venue_id SMALLINT UNSIGNED NOTNULL AUTO_INCREMENT,
    name VARCHAR (144) NOTNULL,
    location ...
    capacity SMALLINT UNSIGNED NOTNULL,
    ...
    PRIMARY KEY(venue_id)
) ENGINE=InnoDB

CREATETABLE seat (
    venue_id SMALLINT UNSIGNED NOTNULL,
    seat_num VARCHAR(30) NOTNULL,
    is_handicap ...,
    strip_num SMALLINT UNSIGNED NOTNULL,  -- see belowPRIMARY KEY(venue_id, seat_num)
) ENGINE=InnoDB

This fails to take care of a venue where you sometimes want to block off the balcony, thereby invalidating some of the seats. Having a different venue and id with most of the info the same might be direction to take.

Be sure to use transactions (BEGIN..COMMIT and FOR UPDATE) where appropriate.

CREATETABLE showing (
    showing_id MEDIUM UNSIGNED NOTNULL AUTO_INCREMENT,
    venue_id SMALLINT UNSIGNED NOTNULL,
    date ...
    notes ...
    PRIMARY KEY(showing_id)
) ENGINE=InnoDB

To deal with seat adjacency, I suggest manually assign each stretch of adjacent seats a "strip" number that stops at aisles, posts, etc. Alas, that is not adequate for a middle section where seat "1" is in the middle, with even numbers going one way and odd going the other way. So K-8 and K-9 are quite far apart, but K-8 and K-10 are adjacent, in spite sorting far apart.

As for confirmed, it "belongs" in reservation. But it might be more convenient for other actions to have it in seat. We may need to work out the SQL statements to make that decision. Also, the SQL statements are necessary for deciding on what secondary INDEXes to have.