can't add foreign key in mysql?

Question

I used MySQL workbench to add a foreign key in a table, but some strange error happened, this is the SQL statement:

ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL  AFTER `declarationId` , 
    ADD CONSTRAINT `goodsId`
        FOREIGN KEY (`goodsId` )
        REFERENCES `tansung`.`Goods` (`goodsId` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
    , ADD INDEX `goodsId` (`goodsId` ASC) ;

When i click apply, the surprise comes out!

ERROR 1005: Can't create table 'tansung.#sql-1b10_1' (errno: 150)

SQL Statement:

ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL  AFTER `declarationId` , 
    ADD CONSTRAINT `goodsId`
        FOREIGN KEY (`goodsId` )
        REFERENCES `tansung`.`Goods` (`goodsId` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
    , ADD INDEX `goodsId` (`goodsId` ASC)


ERROR: Error when running failback script. Details follow.


ERROR 1050: Table 'Declaration' already exists

SQL Statement:

CREATE TABLE `Declaration` (
    `declarationId` int(11) NOT NULL,
    PRIMARY KEY (`declarationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I can't find out any mistake in logic, even can't understand the error, please give me a help.

Aqaba

the error seems pretty self-explanatory; you're trying to create a table called Declaration that already exists... – Woolsey

Make sure you are not running all SQL code in a window. It appears you are running the ALTER TABLE code followed immediately by the CREATE TABLE code...make sure you are only running the ALTER TABLE code to prevent that error. – Narrowminded

@Brian Driscoll the real error comes at the top of the code block ERROR 1005: Can't create table 'tansung.#sql-1b10_1' (errno: 150) – Bedside

i just run a "alter" operation,never "creat" operation,(maybe workbench add it?i confused). – Aqaba

I have the very same problem, and after testing it directly on Server I can only assure you that it's not the Workbench. – Alkaloid

Please give @abhijitcaps the tick mark...it is the correct answer – Polysaccharide

Answer 1

All foreign key names throughout the database must be unique. If you already have a foreign key named 'goodsId', even on another table, you will receive this error.

If the related columns do not have exactly the same type (e.g. INT) and constraints (UNIQUE and such), you will receive that error.

Collude

Answer 2

It can happen because of many reasons. Following are some of the common reasons. You can also say syntactical errors, because of which these kinds of error are thrown.

1. If the FK (Foreign Key) table Engine is using MyISAM and PK (Primary Key) table Engine is using InnoDB. MyISAM does not support foreign key constraints. So, you might want to converting your linking table to InnoDB.

2. All foreign key names throughout the database must be unique. If you already have a foreign key constraint with the same name, even on another table, you will receive this error.

3. If the related columns do not have exactly the same data typetype (e.g. INT) and constraints (UNIQUE and such), you will receive that error.

Dardani

thanks I was not aware that All foreign key names throughout the database must be unique – Lodge

You are welcome @Vinay. Glad that the answer helped you. – Dardani

Mismatched table engines was the culprit for me. Thanks. – Mindszenty

THANK YOU! This was exactly what was going for me. – Unbiased

Answer 3

I'm getting this error when the table being linked to (in your case, Goods) is stored using MyISAM, and the table you're adding the index to (in your case, Declarations) is stored using InnoDB.

You can tell this from the files in the database directory. MyISAM tables will have files like:

table_name.frm
table_name.MYD
table_name.MYI

The InnoDB table will just have:

table_name.frm

MyISAM does not support foreign key constraints. I would suggest converting your Goods table to InnoDB (though, have a look at the documentation first and do some basic research):

ALTER TABLE Goods ENGINE=INNODB;

After making this change, my ADD INDEX operation completed successfully.

Bulletproof

Answer 4

Like the others have said, first make sure the types of the two columns are the same and the database supports it. After that, make sure that the columns that hold the keys to the other tables are valid. I had a problem where I was adding the constraint to an existing column with data in it, and that data didn't match any of the primary keys in the other table so the attempt to create the relationship would fail. Fixing it involved updating all the columns to make sure my column data matched up with the constraint I was trying to make.

Terbium

Answer 5

I discovered that when trying to do this in phpMyAdmin that tables that had a hyphen in the name would only allow one FK and then give errors. I have no idea why but it was easy enough to work around I simply remade the

 CREATE TABLE `something_new` LIKE `something-old`;
 DROP TABLE `something-old`;

YMMV.

Upstate

Answer 6

The type definitions of Goods.goodsId and Declarations.goodsId must be identical, or you will get the errno: 150.

Make sure they are both the same data type, which looks to be goodsId INT(11) NOT NULL in the Declarations table. What is the CREATE TABLE statement for Goods?

Bedside

it may have some relations with that i set "lower_case_table_names=0" – Aqaba

because i need ORM to genenrate entity class,so i want to use upper_case,but mysql in windows only take the lower_case table name,so i set lower_case_table_names=0,but that may have some problems when i add foreign key. – Aqaba

Answer 7

I had the same problem. It seems that there was some data in the child table that was not present in the parent table. You can do an outer join to see the differences and you can assign a valid id for non-matching rows or delete them:

DELETE FROM books
WHERE NOT EXISTS (
    SELECT * FROM users
    WHERE books.user_id = users.id
)

Isomagnetic

Answer 8

When I got that error it was becuase I was trying to update a table that already had data int it and the data didn't meet the FK restrictions.

Dingess

Answer 9

Errno 150 has a lot of causes. If you have SUPER privileges, you should try using

SHOW ENGINE INNODB STATUS

and that will tell you what the cause was. If you don't have SUPER privileges, you need to just go through all the possible causes. You can find how to use the SHOW INNODB STATUS and a list of all the causes here:

MySQL Foreign Key Errors and Errno 150

Delvecchio

Answer 10

A fourth possible problem (to the three proposed by abhijitcaps) is that you didn't make the column you are referencing to a primary key.

Sputnik