Error when i add foreign key in yii

Question

when i add this foreign key using migrate yii display this error:

add foreign key fk_material_userprofile: material (insert_user_ID) references userprofile (userID) 
...exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: 
SQLSTATE[HY000]: General error: 1215 Impossible d'ajouter des contraintes d'index externe. The SQL 
statement executed was: ALTER TABLE `material` ADD CONSTRAINT `fk_material_userprofile` FOREIGN KEY 
(`insert_user_ID`) REFERENCES `userprofile` (`userID`) ON DELETE CASCADE ON UPDATE RESTRICT' in 
E:\framework\db\CDbCommand.php:358

and this my code:

public function up()
{
    $this->addForeignKey("fk_newspaper", "materiallll", "newspaper_ID", "newspaper", "newspaper_ID", "CASCADE", "RESTRICT");
}

and this my database:

CREATE TABLE IF NOT EXISTS `materiallll` (
`material_ID` int(11) NOT NULL AUTO_INCREMENT,
`newspaper_ID` tinyint(4) NOT NULL,
PRIMARY KEY (`material_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

help me please.

please translate into english the error displayed in french. It could help getting an answer faster. — Eduard Uta, Dec 19 '14 at 08:52

score 0 · Accepted Answer · edited May 23 '17 at 12:12

You can see the SQL statement that is being executed:

ALTER TABLE `material` ADD CONSTRAINT `fk_material_userprofile` FOREIGN KEY 
(`insert_user_ID`) REFERENCES `userprofile` (`userID`) ON DELETE CASCADE ON UPDATE RESTRICT'

And you can see that it has nothing to do with the PHP code you've provided:

public function up()
{
$this->addForeignKey("fk_newspaper", "materiallll", "newspaper_ID", "newspaper", "newspaper_ID",          "CASCADE", "RESTRICT");
}

So, it seems like the error you are getting is concerning some other constraint you're creating, which is named "fk_material_userprofile" and is referencing a table called "userprofile", not a table called "newspaper" as your PHP code says.

Also, the table that the foreign key constraint will be added to is called "materiallll" in your PHP code and it's called 'material' in the executed SQL.

Here's a reference to the usage of the Yii addForeignKey() function, and here's a reference to a post that gives proper ADD FK statement.

Error when i add foreign key in yii

1 Answers1