Mysql Foreign Key Constraint Error 150
Contents |
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 mysql errno 150 can't create table more about Stack Overflow the company Business Learn more about hiring developers or
Foreign Key Mysql Example
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community mysql error 1005 (hy000) Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Creating tables with Foreign Keys giving errno: 150 up how to define foreign key in mysql phpmyadmin 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 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`) )
Error 1005 Mysql
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 283k50395569 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:39 add a comment| 17 Answers 17 active oldest votes up vote 194 down vote I had the same problem with ALTER TABLE ADD FOREIGN KEY. After an hour, I found that these conditions must be satisfied to not get error 150: The two tables must have the same engine i.e. ENGINE=InnoDB. (can be others: ENGINE=MyISAM works too) The two tables must have the same charset. The PK column(s) in the parent tab
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 foreign key mysql syntax site About Us Learn more about Stack Overflow the company Business Learn more mssql create table foreign key about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Mysql Error Code 1005
Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL: Can't create http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 table (errno: 150) up vote 122 down vote favorite 19 I am trying to import a .sql file and its failing on creating tables. Here's the query that fails: CREATE TABLE `data` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `value` varchar(15) NOT NULL, UNIQUE KEY `id` (`id`,`name`), CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I exported the .sql from the the same database, I dropped all the tables and now im trying to import it, why is it failing? MySQL: Can't create table './dbname/data.frm' (errno: 150) sql mysql phpmyadmin mysql-error-1005 share|improve this question edited Oct 31 '10 at 0:37 OMG Ponies 199k37360417 asked Oct 31 '10 at 0:24 gtilx 75921018 1 For essentially all the causes of this error, here is an exhaustive resource for what causes errno 150 (and errno 121/other foreign key errors) in MySQL. –John Smith Sep 29 '12 at 0:47 16 I've found that the columns must be identical (even the unsigned flag must match). –Justin Skiles Dec 19 '12 at 19:54 3 @JohnSmith ... where? –Charles Wood Jul 29 '13 at 17:07 3 I suggest reading this blog post that lists 10 possible causes: verysimple.com/2006/10/22/… –Mark Amery Jan 2 '14 at 13:54 1 @trejder Curses! –Charles Wood Nov 7 '14 at 17:16 | show 2 more comments 27 Answers 27 active oldest votes up vote 134 down vote accepted From the MySQL - FOREIGN KEY Constraints Documentation: If you re-create a ta
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as http://dev.mysql.com/doc/en/create-table-foreign-keys.html a Document Store Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax Data Definition Statements ALTER DATABASE Syntax ALTER EVENT Syntax ALTER FUNCTION Syntax ALTER INSTANCE Syntax ALTER LOGFILE GROUP Syntax ALTER PROCEDURE Syntax ALTER SERVER Syntax ALTER TABLE Syntax ALTER TABLE Partition Operations foreign key ALTER TABLE Online Operations in MySQL Cluster ALTER TABLE and Generated Columns ALTER TABLE Examples ALTER TABLESPACE Syntax ALTER VIEW Syntax CREATE DATABASE Syntax CREATE EVENT Syntax CREATE FUNCTION Syntax CREATE INDEX Syntax CREATE LOGFILE GROUP Syntax CREATE PROCEDURE and CREATE FUNCTION Syntax CREATE SERVER Syntax CREATE TABLE Syntax CREATE TABLE ... LIKE Syntax CREATE TABLE ... SELECT foreign key mysql Syntax Using FOREIGN KEY Constraints Silent Column Specification Changes CREATE TABLE and Generated Columns Secondary Indexes and Generated Virtual Columns Setting NDB_TABLE options in table comments CREATE TABLESPACE Syntax CREATE TRIGGER Syntax CREATE VIEW Syntax DROP DATABASE Syntax DROP EVENT Syntax DROP FUNCTION Syntax DROP INDEX Syntax DROP LOGFILE GROUP Syntax DROP PROCEDURE and DROP FUNCTION Syntax DROP SERVER Syntax DROP TABLE Syntax DROP TABLESPACE Syntax DROP TRIGGER Syntax DROP VIEW Syntax RENAME TABLE Syntax TRUNCATE TABLE Syntax Data Manipulation Statements CALL Syntax DELETE Syntax DO Syntax HANDLER Syntax INSERT Syntax INSERT ... SELECT Syntax INSERT DELAYED Syntax INSERT ... ON DUPLICATE KEY UPDATE Syntax LOAD DATA INFILE Syntax LOAD XML Syntax REPLACE Syntax SELECT Syntax SELECT ... INTO Syntax JOIN Syntax UNION Syntax Subquery Syntax The Subquery as Scalar Operand Comparisons Using Subqueries Subqueries with ANY, IN, or SOME Subqueries with ALL Row Subqueries Subqueries with EXISTS or NOT EXISTS Correlated Subqueries Subqueries in the FROM Clause Subquery Errors Optimizing Subqueries Rewriting Subqueries as Joins UPDATE Syntax Transactional and Locking