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.

Answer 1

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
)

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.

Answer 3

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.

Answer 4

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.

Answer 5

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?

Answer 6

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.