Agile Yii Book

Hi,

I am going through Agile Web Application Development with Yii 1.1 and PHP5. In Chapter 8, the reader is asked to set up fixture data for the table tbl_project_user_role. However, doing so seems to cause a FK constraint issue with the ProjectTest::testDelete() method.

Has anyone encountered this problem? Perhaps I am doing something wrong? The quick fix is to remove the foreign key constraint for tbl_project_user_role.project_id to the tbl_project.id field.

Thanks,

Yiier

Hello Yiier,

Are you using MySQL as you are following along with the examples? If so, have you ensured that the foreign key is defined with ON DELETE CASCADE?:

[sql]ALTER TABLE tbl_project_user_role ADD CONSTRAINT FK_user_id FOREIGN KEY (user_id) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE CASCADE;[/sql]

Hello jefftulsa,

Thank you for the quick reply.

Yes, I am using MySQL. The book does not appear to specify any cascading deletes for any of the FK constraints applied to the tbl_project_user_role table. The problem was with the project_id constraint, not the user_id constraint. Did you mean to reference this field instead?

I did try your suggestion (but applied to the project_id field instead) and it worked on the test db with fixtures. I re-ran the unit test and it works now. However, and if I am wrong please forgive me, if we set up the relationship this way on the actual live DB, wouldn’t this delete any project (or user in your case) when the role is deleted? Seems like this would not be intended. Furthermore, it would seem that the test DB’s schema should precisely match that of the live DB?

Thank you for your input,

Yiier

Hello Yiier,

Yes, I did mean to reference the project_id rather than the user_id FK relationship in the previous post:

[sql]ALTER TABLE tbl_project_user_role ADD CONSTRAINT FK_project_id FOREIGN KEY (project_id) REFERENCES tbl_project (id) ON DELETE CASCADE ON UPDATE CASCADE;[/sql]

With this in place, if you delete a row in the "parent" table, i.e. tbl_project, and there are any associated rows (i.e. via the FK project_id) in the tbl_project_user_role table, these will be deleted as well. However, there is no FK defined on tbl_project back to tbl_project_user_role, so simply deleting a row in this table will not delete rows in your tbl_project table.

Of course, you can also take care of this programmatically if you prefer and not define it this way in MySQL. Then, of course, you would need to alter how you delete projects by first manually deleting from any other table that have FKs tied back to the tbl_project.id field.

You are correct that the foreign key is not specified this way in the DDL statement for creating the table. The book is attempting to say database independent, and the low level DDL statements for tables are intended to be general (i.e. not MySQL specific).

If you download the actual code for the application, there are MySQL specific SQL (DDL) statements for all objects in the database and these are defined this way there.

You can get the code chapter_by_chapter from the packt site:

or grab it from github:

git@github.com:jefftulsa/TrackStar.git

Hi jefftulsa,

Your thorough explanation makes perfect sense, and now I have a better understanding of how the cascading deletes in MySQL work. Much appreciated!

Sincerely,

Yiier