CODEWITHSUNDEEP
mysqlreferenceforeign-keyscreate-table
Paul Seattle
Paul Seattle

Reputation: 491

FOREIGN KEY errors when creating TABLE even after every possible combination

I've been banging my head against this one for two days now and finally asking for help.

I created two test tables with minimal data to keep things simple, so at least I have something to work up from, and want to create a third table that I can use to pull data down into my website (I'm hoping to create queries that can pull the data from different tables later, but for now this will have to do).

Here's the info I exported for first TABLE:

CREATE TABLE `about` (
`about_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`about_title` varchar(50) NOT NULL,
`about_description` text NOT NULL,
`creator_id` smallint(5) unsigned NOT NULL,
`about_image` varchar(150) NOT NULL,
PRIMARY KEY (`about_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `about` (`about_id`, `about_title`, `about_description`, `creator_id`, `about_image`) VALUES
(1, 'Exciter', 'This is an awesome album', 1, 'images/depeche_mode_exciter.jpg'),
(2, 'Autobahn', 'This is a great album', 2, 'images/kraftwerk_autobahn.jpg');

Here's the info I exported for second TABLE:

CREATE TABLE `creator` (
`creator_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`creator_name` varchar(50) NOT NULL,
PRIMARY KEY (`creator_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `creator` (`creator_id`, `creator_name`) VALUES
(1, 'Depeche Mode'),
(2, 'Kraftwerk');

And here's the most recent version of what I tried using to create a new TABLE that turns the creator_id into Depeche Mode or whatever the artists is on whatever creator_id row:

CREATE TABLE `about_creator` (
`about_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`about_title` varchar(50) NOT NULL,
`about_description` text NOT NULL,
`about_image` varchar(150) NOT NULL,
`creator_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`about_id`)
FOREIGN KEY (`creator_id`) REFERENCES `creator` (`creator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now seriously, it doesn't get any easier that that surely, but every time I try it I'm getting the following error(s):

#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 'FOREIGN KEY (`creator_id`) REFERENCES `creator` (`creator_id`) ) ENGINE=InnoDB ' at line 8

I'm accessing PHP Version 5.4.4 from phpMyAdmin 3.5.4 running in MAMP version 2.0

Any help most appreciated.

Upvotes: 1

Views: 89

Answers (1)

eggyal
eggyal

Reputation: 125855

You're missing a comma after the definition of your PRIMARY KEY:

CREATE TABLE `about_creator` (
`about_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`about_title` varchar(50) NOT NULL,
`about_description` text NOT NULL,
`about_image` varchar(150) NOT NULL,
`creator_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`about_id`),
FOREIGN KEY (`creator_id`) REFERENCES `creator` (`creator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

See it on sqlfiddle.

Upvotes: 1

Related Questions