circular foreign key. How do i handle them?

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 t开发者_运维问答hey 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?

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

In a multi-tenant scenario, assume you have 2 tables: Subscriber & Contact.

You want to know at a glance who is the Primary Contact for the Subscriber. However, the Contact table must also have a FKEY reference to SubscriberId for partitioning / to be used for federation key etc (SQL Azure).

=======================================================================
Subscriber.sql 
=======================================================================
-- One who has subscribed to Rhipheus
CREATE TABLE [rhipheus].[Subscriber]
(
    [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() CONSTRAINT [PKEY_Subscriber_Id] PRIMARY KEY CLUSTERED,
    [ShortName] NVARCHAR(50) NOT NULL,
    [LegalName] NVARCHAR(255) NOT NULL,
    [SmallLogoPath] NVARCHAR(MAX) NOT NULL,
    [LargeLogoPath] NVARCHAR(MAX) NOT NULL,
    [PrimaryContactId] UNIQUEIDENTIFIER NULL REFERENCES [rhipheus].[Contact]([Id]),
)

====================================================================
Contact.sql
====================================================================
CREATE TABLE [rhipheus].[Contact]
(
    [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() CONSTRAINT [PKEY_Contact_Id] PRIMARY KEY CLUSTERED,
    [SubscriberId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [FKEY_Contact_SubscriberId_Subscriber_Id] REFERENCES [rhipheus].[Subscriber]([Id]),
    [FirstName] NVARCHAR(50) NOT NULL,
    [LastName] NVARCHAR(50) NOT NULL,
)

This used to work 2010 Database project because it used to strip away all column level constraints and create them using separate ALTER scripts.

The way I resolved this in VS.Net 2012 is by declaring the foreign key column as NULLable and adding foreign keys on Subscriber using a separate ALTER statement. Of course, SQL Server project in VS 2012 wouldn't allow me to do it column-level the declaration as it is not able to figure out which table to create first (even when the HINT is right there in the form of NULLable declaration).

=======================================================================
Subscriber.sql 
=======================================================================
-- One who has subscribed to Rhipheus
CREATE TABLE [rhipheus].[Subscriber]
(
    [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() CONSTRAINT [PKEY_Subscriber_Id] PRIMARY KEY CLUSTERED,
    [ShortName] NVARCHAR(50) NOT NULL,
    [LegalName] NVARCHAR(255) NOT NULL,
    [SmallLogoPath] NVARCHAR(MAX) NOT NULL,
    [LargeLogoPath] NVARCHAR(MAX) NOT NULL,
    [PrimaryContactId] UNIQUEIDENTIFIER NULL 
)

====================================================================
Contact.sql
====================================================================
CREATE TABLE [rhipheus].[Contact]
(
    [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() CONSTRAINT [PKEY_Contact_Id] PRIMARY KEY CLUSTERED,
    [SubscriberId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [FKEY_Contact_SubscriberId_Subscriber_Id] REFERENCES [rhipheus].[Subscriber]([Id]),
    [FirstName] NVARCHAR(50) NOT NULL,
    [LastName] NVARCHAR(50) NOT NULL
)

====================================================================
Subscriber.ForeignKeys.sql
====================================================================
ALTER TABLE [rhipheus].[Subscriber] ADD CONSTRAINT [FKEY_Subscriber_PrimaryContactId_Contact_Id] FOREIGN KEY([PrimaryContactId]) REFERENCES [rhipheus].[Contact]([Id])
GO