MYSQL Error # 1005

Question

I have been trying to create a foregin key with nbrseats but I i get the error 1005 all the time. CAn someone help me!?

create table theater (
    name varchar(30) primary key,
    nbrseats int not null
) ENGINE=INNODB;

create table reservation (
    nbr integer auto_increment,
    users_username varchar(30),
    cinemashow_showdate date,
    movies varchar(30),
    nbrseats int not null,
    primary key (nbr),
    foreign key (nbrseats) references theater(nbrseats),
    foreign key (users_username) REFERENCES users(username)
        on delete cascade,
    foreign key (cinemashow_showdate, movies) references cinemashow(showdate, movie_title)
       on delete cascade
) ENGINE=INNODB;

if you do `show engine innodb status`, in the output will be a "last foreign key error" section, which more details why you're getting the 1005. — Marc B, Feb 15 '13 at 14:33

score 1 · Accepted Answer · edited May 23 '17 at 11:56

In order to be a FOREIGN KEY in another table, you must have an index created on theater.nbrseats. And in order to be able to reference a specific row reliably, it should therefore be a UNIQUE index. Otherwise, if you have duplicate values, the referencing table won't be able to discern which row it references. Even though InnoDB will allow you to create the relationship on a non-unique index, it is likely not the behavior you are looking for.

See this question for more info on that bit.

create table theater (
  name varchar(30) primary key,
  nbrseats int not null,
  UNIQUE INDEX `idx_nbrseats` (nbrseats)
) ENGINE=INNODB;

The same will be true of the other FOREIGN KEY definitions in your table reservation, though we do not see their referenced tables posted here. The rules are:

The referenced column must be indexed (independently of any other compound indexes on it)
The referencing column must have exactly the same data type.

This kind of calls into question your design, however. If you are attaching a number of seats to a reservation, will the reservation number of seats exactly match the number available in the theater? Also this means that you could not have 2 theaters with the same number of seats.

You may need to reconsider your design here, and perhaps create a FOREIGN KEY that references theater.name instead of theater.nbrseats.

MySQL creates the indexes automatically now, if need be. Of course, OP could be on an ancient version. — Marc B, Feb 15 '13 at 14:34
@MarcB Since what version? Testing on a not-so-ancient 5.5.23, the index doesn't get created. — Michael Berkowski, Feb 15 '13 at 14:38
Ah. D'oh. misreading the docs. it only auto-creates in the referencing table. the target field in the parent able must be indexed manually. — Marc B, Feb 15 '13 at 14:40

MYSQL Error # 1005

1 Answers1