circular foreign key. How do i handle them?

Question

Much of my sql code is generated (from POD). Now i have issue where table user_data has a FK to ref_status which points back to two different user_data. My code does the following

1. begins a transaction
2. looks at user_data (and adds it to a list)
3. sees ref_status then repeats #2 with it
4. executes the create table ref_status code

Then i get the exception

Foreign key 'FK__...__0F975522' references invalid table 'user_data'.

How do i create the two tables if they use both of eachother as a reference? i thought since it was in the same transaction it would work. I'll also note this code works fine in sqlite with enabling FK support (supported since last month release of System.Data.SQLite). So how am i expected to create these two tables?

Answer 1

Circular foreign keys are not really supported in SQL Server. It is possible to do it if you really want but it's not very useful since you've have no way to insert any data - you can't insert into table A because the required references in table B don't exist and vice versa. The only way would be to create one of the tables without the FK and then add it after the second table is created. Then, to insert data, you'd need to disable one of the FKs and then re-enable it but this is a very resource intensive operation if you've lots of data since it will all need to be re-checked when the FK is re-enabled.

Basically, you either have to live with incomplete declarative referential integrity or, perhaps more wisely, consider remodelling your data as @munisor suggests.

WARNING: the following code smaple demonstrates how to create circular FKs but this really is very bad for your health! I'm sure that in the longer run, you won't be wanting to do this. For example, simply trying to drop either of these tables after this is run is very difficult, you can't simple DROP TABLE!

CREATE TABLE [A]
(
    [AId] int
        NOT NULL
        PRIMARY KEY,
    [BId] int
        NULL
        -- You can't create the reference to B here since it doesn't yet exist!
)

CREATE TABLE [B]
(
    [BId] int
        NOT NULL
        PRIMARY KEY,
    [AId] int
        NOT NULL
        FOREIGN KEY
            REFERENCES [A]
)

-- Now that B is created, add the FK to A
ALTER TABLE [A]
    ADD
        FOREIGN KEY ( [BId] )
        REFERENCES [B]

ALTER TABLE [A]
    ALTER COLUMN [BId]
        int
        NOT NULL