Foreign Key Error 150 Mysql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have foreign key mysql example Meta Discuss the workings and policies of this site About Us
Errno 150 Can't Create Table
Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with how to define foreign key in mysql phpmyadmin 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
Error 1005 (hy000)
like you, helping each other. Join them; it only takes a minute: Sign up MySQL Creating tables with Foreign Keys giving errno: 150 up vote 78 down vote favorite 38 I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting mssql create table foreign key an errno: 150 error and it will not create the table. Here is the SQL for all 3 tables: CREATE TABLE role_groups ( `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`, `name` varchar(20), `description` varchar(200), PRIMARY KEY (`role_group_id`) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `roles` ( `role_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50), `description` varchar(200), PRIMARY KEY (`role_id`) ) ENGINE=InnoDB; create table role_map ( `role_map_id` int not null `auto_increment`, `role_id` int not null, `role_group_id` int not null, primary key(`role_map_id`), foreign key(`role_id`) references roles(`role_id`), foreign key(`role_group_id`) references role_groups(`role_group_id`) ) engine=InnoDB; Any help would be greatly appreciated. mysql foreign-keys errno share|improve this question edited Sep 21 '09 at 23:37 Bill Karwin 282k50394569 asked Sep 21 '09 at 22:55 user176842 1 Could you post the error output and tell us which command (of the three) is causing the error? –dave Sep 21 '09 at 23:08 4 What's with the back-ticks around auto_increment? That's not valid. Auto_increment is a keyword, not an identifier. –Bill Karwin Sep 21 '09 at 23:
here for a quick overview of the site Help Center Detailed answers to any questions you might
Error 1005 Mysql
have Meta Discuss the workings and policies of this site
Foreign Key Mysql Syntax
About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting mysql errno 121 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 http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Foreign Key, Can't create table (errno: 150) up vote 7 down vote favorite 2 I am trying to build the database and tables for my system. But I found that if I don't add the foreign key http://stackoverflow.com/questions/20166573/mysql-foreign-key-cant-create-table-errno-150 in the codes. There is no error. I've used many method try to make the codes works, but it still have error. I am using MySQL 5.5.31, and the codes here: CREATE DATABASE TOS; DROP TABLE TOS.USER CASCADE; DROP TABLE TOS.BILL_HEADER CASCADE; DROP TABLE TOS.TOY CASCADE; CREATE TABLE TOS.USER (User Char(8), Name Char(10), Type Char(1), Password Char(12), PRIMARY KEY(User)); CREATE TABLE TOS.BILL_HEADER (Bill_No Char(10), CTime DateTime, No_Of INTEGER, Cus_No Char(5), DTime DateTime, PRIMARY KEY(Bill_No)); CREATE TABLE TOS.TOY (Toy_Id Char(10), FullN Char(50), ShortN Char(20), Descrip Char(20), Price DECIMAL, Avail Char(1), Cat Char(1), PRIMARY KEY(Toy_Id)); CREATE TABLE TOS.BILL_ITEM (Bill_No Char(10), BSeq_No INTEGER, Toy_Id Char(10), OTime DateTime, Quan INT, DCondition Char(1), PRIMARY KEY(Bill_No,BSeq_No), FOREIGN KEY(Bill_No) REFERENCES TOS.Bill_Header(Bill_No), FOREIGN KEY(Toy_Id) REFERENCES TOS.TOY(Toy_Id)); Error: 1005 - Can't create table 'TOS.BILL_ITEM' (errno: 150) Any help would be greatly appreciated. mysql foreign-keys mysql-error-1005 share|improve this question asked Nov 23 '13 at 19:06 Confucius 1043310 It does appear to work if I remove the DROP statements.. sqlfiddle.com/#!2/08d1e &nd
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have http://dba.stackexchange.com/questions/61280/altering-table-foreign-keys-produces-an-error-errno150-mysql 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 posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their foreign key database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Altering table Foreign Keys produces an error (errno:150) (MySQL) up vote 2 down vote favorite This foreign key mysql is the first database I set up so I apologize if my problem is something obvious. I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. When I try to sync my EER model with the DB I get an error which makes no sense to me. Executing SQL script in server ERROR: Error 1005: Can't create table 'jonijz1_Test.#sql-d7d_1291db' (errno: 150) ALTER TABLE `jonijz1_Test`.`UserGroupLink` ADD CONSTRAINT `fk_UserGroupLink_UserGroup` FOREIGN KEY (`UserGroupID`) REFERENCES `jonijz1_Test`.`UserGroup` (`UserGroupID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_UserGroupLink_Users1` FOREIGN KEY (`UserID`) REFERENCES `jonijz1_Test`.`Users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION SQL script execution finished: statements: 5 succeeded, 1 failed Fetching back view definitions in final form. Nothing to fetch What am I doing wrong? And on a sidenote: would a design like this make any sense or am I approaching this wrong as well? mysql foreign-key mysql-workbench share|improve this question edited Mar 19 '14 at 22:04 nmad 6134621 asked Mar