1064 Mysql Error Foreign Key
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the 1064 mysql error insert workings and policies of this site About Us Learn more about Stack
1064 Mysql Error Code
Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs mysql error 1064 42000 Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join
Mysql Error 1064 Create Table
them; it only takes a minute: Sign up MySQL CREATE TABLE & FOREIGN KEY Error up vote 3 down vote favorite 1 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 mysql error 1064 create user 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. mysql mysql-error-1064 share|improve this question edited Oct 27 '11 at 5:44 mu is too short 284k40549557 asked Oct 27 '11 at 5:40 elithrar 9,48822752 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted You're missing a comma after
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or
Mysql Error 1064 Sqlstate 42000
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Mysql Error 1064 Create Trigger
Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only mysql error 1064 create database takes a minute: Sign up MySQL ERROR 1064 (42000) - even with backticks up vote 1 down vote favorite I must be missing something obvious, but still... I get MySQL ERROR 1064 (42000) when I try to run the following: CREATE http://stackoverflow.com/questions/7912085/mysql-create-table-foreign-key-error TABLE `mgr_user` ( `id` INT NOT NULL AUTO_INCREMENT, `customer_id` INT NOT NULL, `username` VARCHAR(25), `password` VARCHAR(100), INDEX(`customer_id`), FOREIGN KEY `customer_id` REFERENCES `customer`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT ); mysql foreign-keys share|improve this question asked Feb 8 '12 at 16:22 R.V. 2271216 Show the full schema. –shiplu.mokadd.im Feb 8 '12 at 16:35 this is the only other create: –R.V. Feb 8 '12 at 16:48 CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(25), http://stackoverflow.com/questions/9197292/mysql-error-1064-42000-even-with-backticks PRIMARY KEY(id) ); –R.V. Feb 8 '12 at 16:48 1 Please show the error message, which often helpfully reveals where the syntax error is. –pilcrow Feb 8 '12 at 16:53 add a comment| 1 Answer 1 active oldest votes up vote 4 down vote accepted You need to wrap around the foreign key name with parenthesis. Also your auto column must be a key. See bellow CREATE TABLE `mgr_user` ( `id` INT NOT NULL AUTO_INCREMENT, `customer_id` INT NOT NULL, `username` VARCHAR(25), `password` VARCHAR(100), INDEX(`customer_id`), UNIQUE KEY (`id`), -- <- Auto column is should be a key FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT ); After these 2 changes, I got Query OK, 0 rows affected (0.07 sec) share|improve this answer answered Feb 8 '12 at 16:51 shiplu.mokadd.im 36.4k571141 Perfect! Thank you. –R.V. Feb 8 '12 at 16:57 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged mysql foreign-keys or ask your own question. asked 4 years ago viewed 1865 times active 4 years ago Related 0MySQL Error number 150 Create Fails on non existent foreign key1Mysql error
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the http://stackoverflow.com/questions/36876699/why-am-i-getting-mysql-error-1064-on-foreign-key-constraint workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join mysql error them; it only takes a minute: Sign up Why am I getting MySQL error 1064 on foreign key constraint? up vote -1 down vote favorite CREATE TABLE EMP_1 ( EMP_NUM varchar(3), EMP_LNAME varchar(15), EMP_FNAME varchar(15), EMP_INITIAL varchar(1), EMP_HIREDATE datetime, JOB_CODE varchar(3), FOREIGN KEY (JOB_CODE) REFERENCES JOB ); This is the code that is given to create a table that mysql error 1064 is a subset of another table. It is part of an exercise. #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 ' REFERENCES JOB)' at line 8 Is the error message that is output in PHPMyAdmin when the query is run. Where is the syntax off on line 8? After checking w3 schools, and textbook, I cannot see the problem. mysql sql phpmyadmin share|improve this question edited Apr 27 at 3:24 wogsland 2,83081235 asked Apr 26 at 22:10 mango762 12 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote you're missing the referencing foreign key column on the JOB table so it should be something like FOREIGN KEY (JOB_CODE) REFERENCES JOB(job_code_col) so if your JOB table also has a column called JOB_CODE, then the DDL would look like this: CREATE TABLE EMP_1 ( EMP_NUM varchar(3), EMP_LNAME varchar(15), EMP_FNAME varchar(15), EMP_INITIAL varchar(1), EMP_HIREDATE datetime, JOB_CODE varchar(3), FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE) ); share|improv