Code Blog

Displaying 1-1 of 1 result.
2015/05/15 Yii,MySQL

MySQL statements for table dependences 

ALTER TABLE tbl_posttag 
ADD CONSTRAINT fk_codeblog_id 
FOREIGN KEY (post_id) REFERENCES tbl_codeblog (id) ON DELETE CASCADE;
 
ALTER TABLE tbl_posttag 
ADD CONSTRAINT fk_tag_id 
FOREIGN KEY (tagId) REFERENCES tbl_tag (id) ON DELETE CASCADE;
 
However, its easy to run into issues when creating dependences. In that case run this command directly after the error for more detailed information:
SHOW ENGINE INNODB STATUS;

Print the CREATE statement for a certain table is also useful for debugging:
CREATE INDEX CODEBLOG_ID_INDEX ON tbl_codeblog (id);

You need to check that all keys are of exactly the same type (length, unsigned, character encoding etc). The key also needs to be indexed. The table also needs to be InnoDB.
I also had a problem with a table inconsistency. I had already created a mapping between a post and tag in tbl_posttag, but the tag entry had already been deleted. In this case I cannot create the dependency without first removing the incorrect posttag entry.