CODEWITHSUNDEEP
doctrine-orm
Nick Lang
Nick Lang

Reputation: 869

Deleting with cyclic foreign keys

I have two entities that each refer to each other: A Page entity which has reference to defaultUrl and a Url entity which has a reference to a page.

This is cyclical and so once I add a page and a url what reference each other, I cannot delete them.

I can see two ways to fix it but am not sure of the "Doctrine" way to do it.

  1. Make one of the relationships an index only, but not a foreign key constraint
  2. When deleting, turn off foreign key constraint checks

I couldn't figure out how to do either of these... do you know??

Thanks!

Upvotes: 2

Views: 484

Answers (2)

ybull
ybull

Reputation: 1061

Here's the Doctrine way of setting this up. As was mentioned before, you need to set at least one of the foreign keys to be nullable.

Also consider setting the onDelete cascades to SET NULL. That will make the process of deleting even simpler -- you won't have to update the key to NULL before deleting the record.

Your schema.yml could look like this:

Url:
  columns:
    page_id: { type: integer, notnull: true }
  relations:
    Page: { local: page_id, foreign: id }

Page:
  columns:
    default_url_id: { type: integer, notnull: false }  # ALLOWS NULL foreign key here
  relations:
    DefaultUrl: { class: Url, local: default_url_id, foreign: id, onDelete: SET NULL }

Upvotes: 3

Joshua
Joshua

Reputation: 43280

Rule of thumb, when making foreign keys, always make one either nullable or omit one foreign key definition.

This allows cycle breaking.

Simplest case (SQL Syntax but the sampe principle applies):

CREATE TABLE Employee (
    EmployeeId INTEGER NOT NULL IDENTITY(1,1)
    ManagerId INTEGER NOT NULL
    --...
    PRIMARY KEY EmployeeId NONCLUSTERED
    FOREIGN KEY FK_Manager REFRENCES Employee(EmployeeId)
)

Can be loop broken as follows:

CREATE TABLE Employee (
    EmployeeId INTEGER NOT NULL IDENTITY(1,1)
    ManagerId INTEGER NOT NULL
    --...
    PRIMARY KEY EmployeeId NONCLUSTERED
)

or as follows:

CREATE TABLE Employee (
    EmployeeId INTEGER NOT NULL IDENTITY(1,1)
    ManagerId INTEGER NULL
    --...
    PRIMARY KEY EmployeeId NONCLUSTERED
    FOREIGN KEY FK_Manager REFRENCES Employee(EmployeeId)
)

I prefer to, when declaring foreign keys, to always trace the diagram and ensure there are no cycles. I will omit the least dangerous keys until all cycles are broken. This ensures that all tables can be bulk exported followed by bulk imported into another database.

EDIT: I find that when you ask a book expert this question you get an answer like turn off the foreign key during delete. This is wrong for two reasons. First, normal transactional operations should not be altering the schema definitions. Second, the rest of the code expects the foreign key to be there and so does not handle it in application code; however schema modifications have a nasty habit of bleeding across transactions or locking entire tables.

Upvotes: 1

Related Questions