Sql Error Creating table

Question

I have created tables Movies and Location but i seem to be getting a strange error I was wondering if anyone could catch what I'm doing wrong here.

CREATE TABLE ShowTimes
(
   showId int NOT NULL, 
   movieid int NOT NULL,        
   cinemaID int NOT NULL,
   showDate date,
   showTime time,

   PRIMARY KEY (showId),
   FOREIGN KEY ShowTimes (movieid) REFERENCES Movies (movieId),
   FOREIGN KEY (cinemaID) REFERENCES Location (cinemaId)
)

Error:

Error Code: 1005. Can't create table 'galaxy.showtimes' (errno: 150) 0.078 sec

CREATE TABLE ShowTimes
(
   showId int NOT NULL, 
   movieid int NOT NULL, 
   cinemaID int NOT NULL,
   showDate date,
   showTime time,
   PRIMARY KEY (showId),
   FOREIGN KEY (movieid) REFERENCES Movies (movieId),
   FOREIGN KEY (cinemaID) REFERENCES Location (cinemaId)
);

INSERT INTO ShowTimes VALUES (1, 1, 1, '2013-09-20', '17:00:00'),
                             (2, 1, 1, '2013-09-20', '19:00:00'),
                             (3, 3, 4, '2013-09-20', '17:00:00'),
                             (4, 2, 3, '2013-09-20', '15:15:00');

Possible duplicate:http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 — Daniel Cheung, Sep 27 '14 at 01:53

score 1 · Answer 1 · edited Sep 27 '14 at 02:11

1

Simple mistake: look at your first FOREIGN KEY. You are referencing the table ShowTimes. Try removing it.

edited Sep 27 '14 at 02:11

elixenide

44,308
16
74
100

answered Sep 27 '14 at 01:58

Parfait

104,375
17
94
125

+1 To clarify, the problem is that the key is specified as foreign, but refers to the table on which it is being added. That's circular. – elixenide Sep 27 '14 at 02:12

Sql Error Creating table

1 Answers1