Mysql – can’t add foreign key in thesql

foreign-keysMySQLmysql-error-1005

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.

Best Answer

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.

Related Topic