32

Is it acceptable to have a circular reference between two tables on the foreign key field?

If not, how can these situations be avoided?

If so, how can data be inserted?

Below is an example of where (in my opinion) a circular reference would be acceptable:

CREATE TABLE Account
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50)
)

CREATE TABLE Contact
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50),
    AccountID INT FOREIGN KEY REFERENCES Account(ID)
)

ALTER TABLE Account ADD PrimaryContactID INT FOREIGN KEY REFERENCES Contact(ID)
  • 2
    "If so, how can data be inserted" - depends on the DBMS being used. Postgres, Oracle, SQLite and Apache Derby for example allow deferrable constraints which would make this possible. With other DBMS you are out of luck (But I would still dispute the need for such a constraint in the first place) – a_horse_with_no_name Jun 1 '15 at 13:08
14

Since you are using nullable fields for the foreign keys, you can in fact construct a system that works correctly the way you envision it. In order to insert rows into the Accounts table you need to have a row present in the Contacts table unless you allow inserts into Accounts with a null PrimaryContactID. In order to create a contact row without already having an Account row present, you must allow the AccountID column in the Contacts table to be nullable. This allows Accounts to have no contacts, and allows Contacts to have no account. Perhaps this is desirable, perhaps not.

Having said that, my personal preference would be to have the following setup:

CREATE TABLE dbo.Accounts
(
    AccountID INT NOT NULL
        CONSTRAINT PK_Accounts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AccountName VARCHAR(255)
);

CREATE TABLE dbo.Contacts
(
    ContactID INT NOT NULL
        CONSTRAINT PK_Contacts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ContactName VARCHAR(255)
);

CREATE TABLE dbo.AccountsContactsXRef
(
    AccountsContactsXRefID INT NOT NULL
        CONSTRAINT PK_AccountsContactsXRef
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AccountID INT NOT NULL
        CONSTRAINT FK_AccountsContactsXRef_AccountID
        FOREIGN KEY REFERENCES dbo.Accounts(AccountID)
    , ContactID INT NOT NULL
        CONSTRAINT FK_AccountsContactsXRef_ContactID
        FOREIGN KEY REFERENCES dbo.Contacts(ContactID)
    , IsPrimary BIT NOT NULL 
        CONSTRAINT DF_AccountsContactsXRef
        DEFAULT ((0))
    , CONSTRAINT UQ_AccountsContactsXRef_AccountIDContactID
        UNIQUE (AccountID, ContactID)
);

CREATE UNIQUE INDEX IX_AccountsContactsXRef_Primary
ON dbo.AccountsContactsXRef(AccountID, IsPrimary)
WHERE IsPrimary = 1;

This provides the ability to:

  1. Clearly delineate the relationships between contacts and accounts through a cross-reference table the way Pieter recommends in his answer
  2. Maintain referential integrity in a sound, non-circular manner.
  3. Provide for a highly maintainable list of primary contacts through the IX_AccountsContactsXRef_Primary index. This index contains a filter, so it will only work on platforms that support them. Since this index is specified with the UNIQUE option, there can only ever be a single primary contact for each account.

For instance, if you want to display a list of all contacts, with a column denoting the "primary" status, showing primary contacts at the top of the list for each Account, you could do:

SELECT A.AccountName
    , C.ContactName
    , XR.IsPrimary
FROM dbo.Accounts A
    INNER JOIN dbo.AccountsContactsXRef XR ON A.AccountID = XR.AccountID
    INNER JOIN dbo.Contacts C ON XR.ContactID = C.ContactID
ORDER BY A.AccountName
    , XR.IsPrimary DESC
    , C.ContactName;

The filtered index prevents insertion of more than a single primary contact per account, whilst simultaneously providing a quick method of return a list of primary contacts. One could easily imagine another column, IsActive with a non-unique filtered index to maintain a history of contacts per account, even after that contact is no longer associated with the account:

ALTER TABLE dbo.AccountsContactsXRef
ADD IsActive BIT NOT NULL
CONSTRAINT DF_AccountsContactsXRef_IsActive
DEFAULT ((1));

CREATE INDEX IX_AccountsContactsXRef_IsActive
ON dbo.AccountsContactsXRef(IsActive)
WHERE IsActive = 1;
| improve this answer | |
  • 1
    would you say, in general, that circular references should be avoided ? I am of the opinion that they are not bad and have used them to accomplish effective designs. They do make deletes slightly more complicated in that they require and update to NULL in the otherwise would-be-only-parent entity but I find that to be a low price to pay for the convenience. I use them in Postgres, where the FK field is nullable so I create a row with it NULL and then update the FK field to the PK from the child table to pretty much accomplish the same function as is described in the OP – amphibient Jun 15 '17 at 16:54
  • I don't like circular references simply because they tend to needlessly complicate the design, and most of the time don't offer any significant performance benefit worth the trade-off. I'm a fan of Occam's Razor, and as a result tend towards the simplest solution for a given problem. – Max Vernon Jun 15 '17 at 18:42
  • 2
    I'm all for Occam's razor but the abovedescribed design enabled me to avoid some 2nd queries or joins while not necessarily violating the 3rd normal form. I appreciate your feedback – amphibient Jun 15 '17 at 18:44
5

No, it's not acceptable to have circular foreign key references. Not only because it would be impossible to insert data without constantly dropping and recreating the constraint. but because it is a fundamentally flawed model of any and every domain I can think of. In your example I cannot think of any domain in which the relationship between Account and Contact is not N-N, requiring a junction table with FK references back to both Account and Contact.

CREATE TABLE Account
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50)
)

CREATE TABLE Contact
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50),
)

CREATE TABLE AccountContact
(
    AccountID INT FOREIGN KEY REFERENCES Account(ID),
    ContactID INT FOREIGN KEY REFERENCES Contact(ID),

    primary key(AccountID,ContactID)
)
| improve this answer | |
  • 6
    "it would be impossible to insert data" - no, it would not be impossible. Just declare the constraints as deferrable. But I do agree: in almost all cases circular references are a bad design. – a_horse_with_no_name May 31 '15 at 20:44
  • 3
    @a_horse - it's not possible to define a deferrable reference in SQL Server... I know you can in Oracle, just wanted to point out the discrepancy. – Max Vernon Jun 1 '15 at 1:07
  • 2
    @MaxVernon: the question is not only about SQL Server and there are more DBMS than just Oracle that support deferrable constraints - but as I said: I do agree with Pieter that the design itself is wrong (and his solution makes much more sense) – a_horse_with_no_name Jun 1 '15 at 13:10
  • 5
    Leaving aside the specifics of any one example, in general terms there is nothing necessarily wrong or "flawed" about having reciprocal (i.e. "circular") referential integrity constraints. This is in effect just an example of a Join Dependency. Join dependencies are a good thing in principle if your DBMS allows you to implement them. It's just that in SQL DBMSs it isn't very easy to implement complex dependencies between tables. – nvogel Jun 3 '15 at 19:28
  • 6
    @Pieter, 1-1 isn't the only example of a join dependency, and it's not even a particularly special case. There are cases where join dependency constraints make perfect sense. – nvogel Jun 10 '15 at 22:37
1

You could have your external object point to the primary contact, rather than to the account. Your data would look like this:

CREATE TABLE Account
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50)
)

CREATE TABLE Contact
(
    ID INT PRIMARY KEY IDENTITY,
    Name VARCHAR(50),
    AccountID INT FOREIGN KEY REFERENCES Account(ID)
)

CREATE TABLE AccountOwner (
    Other Stuff Here . . .
    PrimaryContactID INT FOREIGN KEY REFERENCES Contact(ID)
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.