41

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)
1
  • 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) Jun 1 '15 at 13:08
19

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;
0
6

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)
)
4
  • 11
    "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. May 31 '15 at 20:44
  • 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
  • 2
    @sqlvogel: I am a data integrity constraint evangelist; but if two entities are constrained to be in 1-1 correspondence, a circular FK is the wrong way to enforce that constraint. Jun 10 '15 at 22:09
  • 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)
)

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.