ForeignKey should also take string arg instead of class name

Circular foreign-key references are tricky to get right. INSERTs, DELETEs, even table creation: you've got to do everything in the right order. It is possible, but it should probably wait until after a 1.0 release.

The only way to even get circular foreign-key relations into the database in the first place is to split out the table creation from the adding of foreign-key constraints. The way the code currently works, we do the equivalent of:

CREATE TABLE address (
    ...
    person_id INTEGER NOT NULL REFERENCES person (id),
    ...
)

But that will only work if the "person" table already exists at that point. If "person" also needs to point to "address", and address back to person, something like the following is needed:

CREATE TABLE person (
    ...
    address_id INTEGER,
    ...
)
CREATE TABLE address (
    ...
    person_id INTEGER,
    ...
)
ALTER TABLE person ADD FOREIGN KEY (address_id) REFERENCES address;
ALTER TABLE address ADD FOREIGN KEY (person_id) REFERENCES person;

This works in PostgreSQL and MySQL; SQLite doesn't have the ALTER TABLE ADD FOREIGN KEY syntax at all. But then, SQLite doesn't actually enforce foreign keys (see http://www.sqlite.org/omitted.html), so that omission doesn't actually hurt.

Both PostgreSQL and MySQL let you insert NULLs in a FOREIGN KEY field (as long as you haven't declared it NOT NULL, of course). This can be the only way to insert data that contains circular references:

BEGIN;
CREATE TABLE spam (
    id SERIAL PRIMARY KEY,
    eggs_id INTEGER
)
CREATE TABLE eggs (
    id SERIAL PRIMARY KEY,
    spam_id INTEGER
)
ALTER TABLE spam ADD FOREIGN KEY (eggs_id) REFERENCES eggs;
ALTER TABLE eggs ADD FOREIGN KEY (spam_id) REFERENCES spam;
COMMIT;
BEGIN;
INSERT INTO TABLE spam (eggs_id) VALUES (3);  -- Will fail if there isn't already an eggs_id 3
ROLLBACK;
BEGIN;
INSERT INTO TABLE spam (eggs_id) VALUES (NULL);
-- Get the just-inserted id; let's say it was 7
INSERT INTO TABLE eggs (spam_id) VALUES (7);
-- Get the just-inserted id; let's say it was 3
UPDATE TABLE spam SET eggs_id=3 WHERE spam.id = 7;
COMMIT;

Deletes from two self-referencing tables also take an intermediate step:

-- Assume the above SQL just ran:
BEGIN;
DELETE FROM spam WHERE spam.id = 7;  -- Will fail: "violates foreign key constraint"
ROLLBACK;
BEGIN;
UPDATE spam SET eggs_id=NULL WHERE eggs_id=3;  -- Clear out the references first
DELETE FROM eggs WHERE eggs_id=3;  -- Now we can delete the row
UPDATE eggs SET spam_id=NULL WHERE spam_id=7;
DELETE FROM spam WHERE spam_id=7;
COMMIT;

How does this relate to Django? Besides the obvious observation that circular foreign-key references are tricky, and that this feature should probably be left until after a 1.0 release, here's what I see as being necessary if this feature is to work at all:

• We'll need to track foreign-key reference cycles in the model API, whether they involve two tables or twenty.
• If a foreign-key reference cycle exists, special-case code will need to be called to deal with it:
  • In a DELETE, first set all the foreign keys to NULL one table at a time, then do the DELETE one table at a time.
  • In an INSERT (of enough rows in enough tables to form one complete loop of the cycle): leave the foreign-key columns NULL until all rows have been inserted, then go through and do a round of UPDATE statements to set the foreign-key columns.
• Table creation code will need to be changed to first create all the tables, then add the FOREIGN KEY constraints.
• Table deletion code will likewise need to be changed to first remove the constraints, then remove the actual tables.

There's probably a lot more than I'm omitting, but that's a start.

The correct way to handle this with PostgreSQL is to make deferr checking of constraints, and then do the circular insert or delete inside a transaction with deferred foreign key checking. See SET CONSTRAINTS for setting these per transaction. By making the foreign key INITIALLY DEFERRED, all transactions can do circular operations.

This has been superceded by #1662.