Auto_Increment: how to auto_increment a combined-key (ERROR 1075)

Question

I am new to mySQL development and I'm trying to set a simple hotel reservation database. One part of the database is the list of tables below:

Hotels
Rooms

The relationship is: One hotel contains many rooms, but one room belongs to one specific hotel (one-to-many relationship). So I designed the table like this:

CREATE TABLE Hotels (
    HotelName CHAR(50) NOT NULL UNIQUE,
    HotelEmail CHAR(200) NOT NULL,
    Category CHAR(1) NOT NULL,
    Street CHAR(200),
    StreetNumber CHAR(5),
    PostalCodhotelse CHAR(10),
    City CHAR(20),
    Country CHAR(30),
    Phone CHAR(10),
    PRIMARY KEY (HotelName)
);

Rooms table contains the rooms with the primary key of the table combined with HotelName and RoomNumber. At this point, I want every time I insert a new HotelName, theauto_increment (RoomNumber`) to be reset, i.e.:

Hotel   RoomNumber
------  ----------
HotelA  1
HotelA  2
HotelA  3
HotelB  1
HotelB  2
HotelC  1

So I designed the table Rooms like this:

CREATE TABLE Rooms (
    HotelName CHAR(200) NOT NULL,
    RoomNumber int auto_increment,
    RoomType CHAR(30),
    NumOfBeds INT Unsigned,
    Description TEXT,
    PRIMARY KEY (HotelName, RoomNumber),
    FOREIGN KEY (HotelName)
    References hotels (HotelName)
    ON UPDATE cascade
);

However, when I try to execute the last SQL statement to create the table, I get the message:

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

My question is how to set the combined key to the table so I can insert RoomNumber every time I change the HotelName?

Make Roomnumber primary_key and use an unique key on (hotelname,roomnumber) — Mihai, May 21 '14 at 22:05
Your storage engine have to be MyISAM, please check http://stackoverflow.com/questions/23597482/how-do-i-create-contextual-auto-increment-in-mysql — VMai, May 21 '14 at 22:09

score 0 · Answer 1 · answered May 21 '14 at 22:10

use auto_increment int or big int fields that are defined as primary keys in both tables, rewrite the rooms table to be a (big) int FK reference rather than by name. The room number itself (on insert) can't be defaulted to auto increment, but this is not a bad thing, as I've seen many hotels who have rooms that cycle from 112 to 200 because each floor in a given building is only 12 rooms large.

score 0 · Accepted Answer · edited May 23 '17 at 11:57

InnoDB tables do not support auto-increment that tracks different max values per hotel, in the way you describe. InnoDB tracks only one auto-increment value per table.

MyISAM tables do support this feature, but that only works because MyISAM does table-level locking, so it is able to scan the table for the largest id value for the respective hotel. Also MyISAM does not support rollback, so it's a bit less likely to produce gaps in the sequence.

But in spite of this, I recommend against using MyISAM.

In short: you can make a compound primary key, but you can't use auto-increment to populate it. You have to give specific integers in the INSERT statement.

score 0 · Answer 3 · answered May 21 '14 at 22:34

Why is Room Number an auto inc?

What about suites, what about hotels where room numbers have gaps?

I personally would be tempted by a room_id auto inc surrogate, but that's another issue and in general a highly opinionated one.

Room number is a natural key, it should not be auto-incrementing.

score 0 · Answer 4 · answered May 22 '14 at 14:32

I tried to re-write the table-rooms like the code below:

CREATE TABLE Rooms (
HotelName CHAR(200) NOT NULL,
RoomNumber int NOT NULL,
RoomType CHAR(30),
NumOfBeds INT Unsigned,
Description TEXT,
PRIMARY KEY (RoomNumber),
UNIQUE KEY (HotelName, RoomNumber)

);

and I add a before-insert trigger on that table as I write:

CREATE TRIGGER room_number BEFORE INSERT ON rooms 
FOR EACH ROW
begin
   set new.RoomNumber = (select ifnull((select max(RoomNumber)+1 from rooms where HotelEmail = new.HotelEmail), 1)); end;

but, the increment sequence didn't work when i tried to insert values just the way I insert below, and I got "Error Code: 1062. Duplicate entry '1' for key 'PRIMARY' " which is correct, I think.

INSERT INTO rooms (HotelName, RoomType, NumOfBeds, Description) VALUES ('HotelA', 'Executive', 3, NULL);
INSERT INTO rooms (HotelName, RoomType, NumOfBeds, Description) VALUES ('HotelA', 'Deluxe', 4, NULL);
INSERT INTO rooms (HotelName, RoomType, NumOfBeds, Description) VALUES ('HotelB', 'Economy', 2, NULL);
INSERT INTO rooms (HotelName, RoomType, NumOfBeds, Description) VALUES ('HotelB', 'Deluxe', 4, NULL);

Auto_Increment: how to auto_increment a combined-key (ERROR 1075)

4 Answers4