Alex Rivera | Logout

can't add foreign key in mysql?

Asked 2011-04-22T17:22:25.323
13

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.

Edit
Report

1 Answer

0

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

answered 2012-06-15T18:01:24.717

Your Answer