StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>Basically, you either have to live with incomplete declarative referential integrity or, perhaps more wisely, consider remodelling your data as @munisor suggests.</p> <p>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 <code>DROP TABLE</code>!</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload