Round foreign key. How do I handle them?
Most of my sql code is generated (from POD). Now I have a problem where the user_data table has an FK for ref_status that points to two different user_data. My code does the following
- starts a transaction
- looks at user_data (and adds it to the list)
- sees ref_status and then repeats # 2 with it
- executes the create table ref_status code command
Then I get an exception
Foreign key 'FK__...__0F975522' references invalid table 'user_data'.
How do I create two tables if they use both of each other as a reference? I thought since it was in the same transaction this would work. I will also note that this code works great in sqlite with FK support (supported since last month's System.Data.SQLite release). So how am I supposed to create these two tables?
Circular foreign keys are not supported on SQL Server. This can be done if you really want, but it is not very useful since you have no way to insert any data - you cannot insert into table A because the required references in table B do not exist and vice versa. The only way is to create one of the tables without FK and then add it after creating the second table. Then, to insert data, you need to turn off one of the FKs and then turn it back on, but this is a very resource intensive operation if you have a lot of data, since all of this will need to be rechecked when the FK is turned on again.
Basically, you either have to deal with incomplete declarative referential integrity, or perhaps more sensibly, think about how to reorganize your data as @munisor suggests.
WARNING: The following smaple code demonstrates how to create circular FKs, but this is very bad for your health! I’m sure you don’t want to do this in the long run. For example, just trying to delete any of these tables after that is very difficult, you can't just 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
a source to share
In a multi-tenant scenario, suppose you have 2 tables: subscriber and contact.
You want to know at a glance who the Subscriber's Primary Contact is. However, the Contact table must also have a FKEY reference to the SubscriberId for partitioning / to use for the join 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 project was used to work with the 2010 database because it used ALTER to remove all constraints at the column level and create them using separate scripts.
The way I solved it in VS.Net 2012 is to declare the foreign key column as NULLable and add foreign keys for the subscriber using a separate ALTER statement. Of course, a SQL Server project in VS 2012 would not allow me to do a column-level declaration as it cannot figure out which table to create first (even if the HINT is there, in the form of a 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
a source to share