Can't delete objects due to foreign key constraints

Question

This is a strange one

Take this schema:

Contact:
  actAs: [Timestampable,SoftDelete]
  columns:
    first_name:  { type: string(255), notnull: true }
    second_name:  { type: string(255), notnull: true }
  relations:
    Forums:
      class: Forum
      refClass: ContactForum
      local: forum_id
      foreign: contact_id 
      foreignAlias: Contacts
    ContactForums:
      local: id
      foreign: contact_id
      class: ContactForum
      type: many
      foreignType: one
      cascade: [delete]

Forum:
  actAs: [Timestampable,SoftDelete]
  columns:
    name:  { type: string(255), notnull: true }
  relations:
    ContactForums:
      class: ContactForum
      local: id
      foreign: forum_id
      type: many
      cascade: [delete]

ContactForum:
  actAs: [Timestampable]
  columns:
    contact_id:  { type: integer, primary: true }
    forum_id: { type: integer, primary: true }

Then if we associate a couple of Forum objects to a Contact object and then try and delete this Contact object we get this error message:

Integrity constraint violation: 19 contact_forum.created_at may not be NULL

If you add SoftDelete to the link table the delete works correctly, and so does the SoftDelete. However we don't want SoftDelete on the link table as it means our primary keys don't work correctly. Is this a bug?

score 1 · Accepted Answer · answered Jul 21 '10 at 12:47

1

This is a doctrine bug. Bug report here: http://www.doctrine-project.org/jira/browse/DC-795 with patch to fix.

answered Jul 21 '10 at 12:47

johnwards

1,901
3
18
27

As doctrine 1.2 trac is down, the best way to fix this is to define onDelete: CASCADE for the relation (and probably add a softDelete behaviour to the other entity) – glerendegui Feb 19 '16 at 19:55

jdd · Answer 2 · 2010-07-21T03:59:41.407

0

I think your ids for your many to many relationship are screwed up, assuming that symphony is using Doctrine 1.2.2. Try this:

Contact:
  actAs: [Timestampable,SoftDelete]
  columns:
    first_name:  { type: string(255), notnull: true }
    second_name:  { type: string(255), notnull: true }
  relations:
    Forums:
      refClass: ContactForum
      local: contact_id
      foreign: forum_id 
      cascade: [delete]

Forum:
  actAs: [Timestampable,SoftDelete]
  columns:
    name:  { type: string(255), notnull: true }
  relations:
    Contacts:
      refClass: ContactForum
      local: forum_id
      foreign: contact_id
      cascade: [delete]

ContactForum:
  actAs: [Timestampable]
  columns:
    contact_id:  { type: integer, primary: true }
    forum_id: { type: integer, primary: true }

In the relationship, when specifying the class with refClass, local and foreign mean "the column on this other class' table that represents me" and "the column on this other classes table that represents the other", respectively.

Edit: I'm not sure if your definition for the Forums relation under Contact is correct either. Assuming you just need a many-to-many relationship, it can be removed.

Double Edit: Look. Here is all the schema you should need for properly functioning and cascading many-to-many relationships. You shouldn't need two relationships defined to properly cascade deletes.

edited Jul 21 '10 at 03:59

answered Jul 20 '10 at 21:00

jdd

4,301
1
27
28

The schema is correct. I don't need to define the rel on the forum table. The problem is with the contact table and the link table. The second relationship is defined to make sure the deletes cascade to the link table. This is what breaks. Add soft delete to the link table and it is fine. I want to use application deletes not db cascading deletes. – johnwards Jul 20 '10 at 21:43
If you don't want to use db cascading deletes, than don't use `cascade: [delete]` in your schema. It generates sql. Also, every source I've seen for writing many-to-many relationships using Doctrine does not define them the way you are. If you're sure that's what you want, then fine, but it sure looks like you have some redundant, if not incorrect stuff there. – jdd Jul 21 '10 at 03:55
2

`cascade: [delete]` is application deletion. `onDelete: [CASCADE]` is db cascading deletion. See http://www.doctrine-project.org/projects/orm/1.2/docs/manual/defining-models%3Atransitive-persistence%3Adatabase-level-cascades/en – richsage Jul 21 '10 at 07:40
See this answer which solves the users problem at why I am putting the extra relationship on Contact. http://stackoverflow.com/questions/3100674/symfony-fixtures-set-ondelete-cascade-on-refclass-table/3102618#3102618 – johnwards Jul 21 '10 at 07:42
If I take off the second relationship, "ContactForums", from Contact then it removes the cascade. You can't put an application delete on the ContactForum link table, that just doesn't work. As I am using SoftDelete I need application level deletes not DB level deletes as Contact isn't really getting deleted. – johnwards Jul 21 '10 at 07:49
Just noticed your other edit. You can't put cascades on a M-M relationship like you have done. If you don't believe me then download this sandbox. http://dl.dropbox.com/u/8354765/sf_sandbox_del.zip and run the test ./symfony test:unit Contact – johnwards Jul 21 '10 at 08:00
Oh, you're right, I put them on there backwards :/ In any case, that doesn't really help things out. It's clear why you're defining multiple relations now... as for why your problem is happening, I'm a bit stumped. It may actually be a bug. – jdd Jul 21 '10 at 11:16
I do believe it is a bug. I think I know why too. I'll update the question with more info – johnwards Jul 21 '10 at 11:56

Can't delete objects due to foreign key constraints

2 Answers2