CODEWITHSUNDEEP
symfonydoctrine-orm
Tomsgu
Tomsgu

Reputation: 1016

Doctrine2 + symfony2 Keep history table on delete with FK

I have 2 tables. One let's say organizations and a second one is organization_history. Then I have a table actions_history with concrete action,... But it isn't so important in this case. In Organization history I keep revision and organizationId. Everything works well until DELETE a table organization. My idea is to keep every action in history table. On INSERT, UPDATE and DELETE action. But problem is when I try to delete organization table. I got this output:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`database`.`base_organizationsHistory`, CONSTRAINT `FK_EAF457A532C8A3DE` FOREIGN KEY (`organization_id`) REFERENCES `base_organizations` (`id`))

1) Is it possible to just delete organization table and keep the history table and ignore foreign key. Or does exist a different and even clear solution?

2) I am thinking also about lifecycle callbacks. In INSERT and UPDATE action I connected tables easily, but when I can create history table on delete action it's not possible to use postRemove callback, because then I don't have the old data for copy to the history. And if I use preRemove callback it's not so clear. Does exist some better idea to do it?

Organization.orm.yml:

BaseBundle\Entity\Organization:
type: entity
table: base_organizations
id:
    id:
        type: integer
        id: true
        generator:
            strategy: AUTO
fields:
    name:
        type: string
        length: 128
    type:
        type: string
        length: 64
oneToMany:
    organizationHistory:
        targetEntity: OrganizationHistory
        mappedBy: organization
        nullable: true

lifecycleCallbacks:
  postPersist: [saveInsertHistory]
  postUpdate: [saveUpdateHistory]
  preRemove: [saveDeleteHistory]

and OrganizationHistory.orm.yml

BaseBundle\Entity\OrganizationHistory:
type: entity
table: base_organizationsHistory
uniqueConstraints:
    organization_history_idx:
        columns: [ organizationId, revision ]
id:
    id:
        type: integer
        id: true
        generator:
            strategy: AUTO
fields:
    name:
        type: string
        length: 128
    type:
        type: string
        length: 64
    revision:
        type: integer
        nullable: false
    organizationId:
        type: integer
        nullable: false
    createdAt:
        type: datetime
        nullable: false
    updatedAt:
        type: datetime
        nullable: false
manyToOne:
    organization:
        targetEntity: Organization
        inversedBy: organizationHistory
        nullable: true
lifecycleCallbacks:
     preUpdate: [ setUpdateTimestamp ]
     prePersist: [ setCreationTimestamp, setUpdateTimestamp ]

Upvotes: 0

Views: 405

Answers (1)

Tomas Tibensky
Tomas Tibensky

Reputation: 821

you cannot delete data from one table and also want to store data related to organization in other table. Thats why relations are made for... But, you are using symfony, do "soft delete" which will just mark entity as deleted, but data still will be in your database.

maybe you can start here https://github.com/Atlantic18/DoctrineExtensions/blob/master/doc/softdeleteable.md

Upvotes: 1

Related Questions