[DBAL-58] Schema tool does not see difference between onDelete="NO ACTION" and onDelete="RESTRICT" Created: 11/Aug/10  Updated: 31/Oct/10

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Jan Obrátil Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None

Attachments: File Example.php    

 Description   

If database has ON DELETE CASCADE, schema tool detects change to NO ACTION and RESCRICT.
If database has ON DELETE RESCRICT, schema tool detects change only in case of CASCADE.
If database has ON DELETE NO ACTION, schema tool detect change only in case of CASCADE.

Detecting of changes is done by:
doctrine orm:schema-tool update --dump-sql
and
doctrine orm:validate-schema

 Comments   
Comment by Benjamin Eberlei [ 11/Aug/10 ]

which database version do yo uuse? can you paste an example schema?

Comment by Jan Obrátil [ 11/Aug/10 ]

This is example script with 2 testing entities.

Comment by Jan Obrátil [ 11/Aug/10 ]

I have attached script with two entities.

I have this database version:
$ mysql --version
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

Check line 44. There is CASCADE.
Updating schema by: $ doctrine orm:schema-tool:update

Change CASCADE to NO ACTION
$ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE NO ACTION

everything ok, so: $ doctrine orm:schema-tool:update

Change NO ACTION to RESTRICT
$ doctrine orm:schema-tool:update --dump-sql
// no output!!

Change RESTRICT to CASCADE
$ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE CASCADE

everything ok, so: $ doctrine orm:schema-tool:update

Change CASCADE to RESTRICT
$ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE RESTRICT

everything ok, do: $ doctrine orm:schema-tool:update

Change RESTRICT to NO ACTION
$ doctrine orm:schema-tool:update --dump-sql
// no output!!

So there is no way to update database schema from RESTRICT to NO ACTION and reverse!

Comment by Benjamin Eberlei [ 31/Oct/10 ]

Hm you are right, however for MySQL NO ACTION is the same as RESTRICT as stated by http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Generated at Sat May 25 01:45:20 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.