CODEWITHSUNDEEP
mysqldatabase
Lime3003
Lime3003

Reputation: 123

MYSQL Error # 1005

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;

Upvotes: 2

Views: 852

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

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.

Upvotes: 1

Related Questions