Is it acceptable to have circular foreign key references\How to avoid them? 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)
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:


Clearly delineate the relationships between contacts and accounts through a cross-reference table the way Pieter recommends in his answer
Maintain referential integrity in a sound, non-circular manner.
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;