CODEWITHSUNDEEP
mysqlmysql-error-1064
elithrar
elithrar

Reputation: 24260

MySQL CREATE TABLE & FOREIGN KEY Error

I'm not a DB guy, but I've been hacking away at this one for a bit and can't seem to nail the issue. I've read the relevant doc page (http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html) and I can't see where my syntax may be an issue.

ERROR 1064 (42000) at line 84: 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 (user_id) REFERENCES user(id)
    ON DELETE RESTRICT ON UPDATE C' at line 5

This is the relevant SQL - order(id) and user(id) are auto incrementing int(10) fields in their respective tables.

DROP TABLE IF EXISTS `user_orders`;
CREATE TABLE `user_orders` (
`user_id` int(10) unsigned NOT NULL default '0',
`order_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`user_orders_user_id`, `user_orders_order_id`)
FOREIGN KEY (user_id) REFERENCES user(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (order_id) REFERENCES order(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
) AUTO_INCREMENT=50;

Help would be appreciated - especially if you can explain what I've missed.

Upvotes: 3

Views: 10336

Answers (2)

HelpVampire666
HelpVampire666

Reputation: 124

I believe you have no idea what you're doing. Your code is full of errors.

user_id int(10) unsigned NOT NULL default '0', order_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (user_orders_user_id, user_orders_order_id)

Where do the user_orders_user_id come from. And why double. Take the time to read your code. It should be , PRIMARY KEY (user_id, order_id)

FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (order_id) REFERENCES order(id) ON DELETE RESTRICT ON UPDATE CASCADE, ) AUTO_INCREMENT=50;

I suggest you take a different approach. First re-order your commas, so you can easily switch them. Then Place # in front of your FK lines. 

    DROP TABLE IF EXISTS `user_orders`;
CREATE TABLE `user_orders` 
( user_id int(10) unsigned NOT NULL default '0'
, order_id int(10) unsigned NOT NULL default '0'
, PRIMARY KEY (`user_id`, `order_id`)
#, FOREIGN KEY (user_id) REFERENCES userS(id)  ON DELETE RESTRICT ON UPDATE CASCADE
# , FOREIGN KEY (order_id) REFERENCES order_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB 
# AUTO_INCREMENT=50 
DEFAULT CHARSET=utf8
;

Simplify. This runs at least. Then uncomment 1 line at a time.

Type

SHOW ENGINE INNODB STATUS;

whenever you get weird mysql errors. Look for 

------------------------
LATEST FOREIGN KEY ERROR
------------------------
160323 10:25:43 Error in foreign key constraint of table DEMO/user_orders:
FOREIGN KEY (user_id) REFERENCES userS(id)  ON DELETE RESTRICT ON UPDATE CASCADE

This leads to the following solution.

    CREATE TABLE `USERS` 
( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
, `NAME` VARCHAR(10) NOT NULL
, `EMAIL` VARCHAR(20) NOT NULL
, `ACTIVE` BOOLEAN NOT NULL default 0
, CONSTRAINT PK PRIMARY KEY (`ID`) USING BTREE
, UNIQUE KEY `UK_ID` (`ID`) USING BTREE
, UNIQUE KEY `UK_NAME` (`NAME`)
, UNIQUE KEY `UK_EMAIL` (`EMAIL`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user_orders`;
CREATE TABLE `user_orders` 
( user_id int(10) unsigned NOT NULL # default '0'
, order_id int(10) unsigned NOT NULL # default '0'
, PRIMARY KEY (`user_id`, `order_id`)
, FOREIGN KEY (user_id) REFERENCES USERS(id)  ON DELETE RESTRICT ON UPDATE CASCADE
# , FOREIGN KEY (order_id) REFERENCES order_id ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB 
# AUTO_INCREMENT=50 
DEFAULT CHARSET=utf8
;

Upvotes: -1

mu is too short
mu is too short

Reputation: 434685

You're missing a comma after your PK:

PRIMARY KEY (`user_orders_user_id`, `user_orders_order_id`), -- <-------
FOREIGN KEY (user_id) REFERENCES user(id) ...

And you'll want to use the InnoDB engine if you want your foreign keys enforced:

) AUTO_INCREMENT=50 ENGINE=InnoDB;

Upvotes: 5

Related Questions