PDA

View Full Version : Foreign Key related MySQL error



devil_vin
09-27-2007, 03:53 PM
Hi..guys! I having l error in query statement related to foreign key creation,

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`reserve_id`)
FOREIGN KEY (`movie_id`) REFERENCES `movie` (`movie_id`)
FOR' at line 9


The table below can't be loaded,


CREATE TABLE `reserve` (
`reserve_id` int(4) NOT NULL auto_increment,
`date` date NOT NULL,
`time` time NOT NULL,
`seat_qty` int(2) NOT NULL,
`ref_code` varchar(50) collate latin1_general_ci NOT NULL,
`movie_id` int(4) NOT NULL,
`member_id`int(4) NOT NULL
PRIMARY KEY (`reserve_id`)
FOREIGN KEY (`movie_id`) REFERENCES `movie` (`movie_id`)
FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;



The foreign keys are refered to these two tables


CREATE TABLE `movie` (
`movie_id` int(4) NOT NULL auto_increment,
`name` varchar(100) collate latin1_general_ci NOT NULL,
`director` varchar(50) collate latin1_general_ci NOT NULL,
`casting` varchar(1000) collate latin1_general_ci NOT NULL,
`duration` time NOT NULL,
`classification` char(4) collate latin1_general_ci NOT NULL,
`language` char(30) collate latin1_general_ci NOT NULL,
`synopsis` varchar(3000) collate latin1_general_ci NOT NULL,
`screeningTime` varchar(200) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`movie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8 ;




CREATE TABLE `member` (
`member_id` int(4) NOT NULL auto_increment,
`name` varchar(50) collate latin1_general_ci NOT NULL,
`email` varchar(50) collate latin1_general_ci NOT NULL,
`password` varchar(15) collate latin1_general_ci NOT NULL,
`telephone` char(10) collate latin1_general_ci NOT NULL,
`address` varchar(70) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

devil_vin
09-27-2007, 05:13 PM
Problem resolved.I forgot some commas..

kiman
01-24-2013, 02:21 AM
Oh my god, was looking for THIS answer for a hour now. Thank you!:)