Circular dependency between 2 table with one to one and one to many relationship I have 2 tables in SQL database, User and Company.  

User belongs to one company.

So user table has UserID &amp; CompanyID (foreign key)

Company can have multiple users. One of them will be the company owner.

So company table has CompanyID &amp; UserID (foreign key)

One user can be the owner of only one company. How can I enforce that?

Is it ok to have such a circular reference? Or should I have a third table CompanyOwner with CompanyID and UserID columns?
In SQL Server, I might accomplish this using a cross reference table between "companies" and "users" where only a single row can be an owner for a given company, via the presence of a unique non-clustered index.

For example:

USE tempdb; --create this test in tempdb

IF OBJECT_ID(N'dbo.CompaniesUsers', N'U') IS NOT NULL
DROP TABLE dbo.CompaniesUsers;

IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;

IF OBJECT_ID(N'dbo.Companies', N'U') IS NOT NULL
DROP TABLE dbo.Companies;
GO

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserName varchar(100) NOT NULL
        CONSTRAINT UQ_Users_UserName
        UNIQUE
);

CREATE TABLE dbo.Companies
(
    CompanyID int NOT NULL
        CONSTRAINT PK_Companies
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CompanyName varchar(100) NOT NULL
        CONSTRAINT UQ_Companies_CompanyName
        UNIQUE
);

CREATE TABLE dbo.CompaniesUsers
(
    CompanyID int NOT NULL
        CONSTRAINT FK_CompanyUsers_CompanyID
        FOREIGN KEY
        REFERENCES dbo.Companies(CompanyID)
    , UserID int NOT NULL
        CONSTRAINT FK_CompanyUsers_UserID
        FOREIGN KEY
        REFERENCES dbo.Users(UserID)
    , CompanyOwner bit NOT NULL
        CONSTRAINT DF_CompanyUsers_Owner
        DEFAULT ((0))
);

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers
ON dbo.CompaniesUsers(CompanyID, CompanyOwner)
INCLUDE (UserID)
WHERE CompanyOwner = 1;


Here I'll insert some test data to show how this works.  First, two "users":

INSERT INTO dbo.Users (UserName)
VALUES ('owner 1')
    , ('owner 2');


And, now, a "Company":

INSERT INTO dbo.Companies (CompanyName)
VALUES ('Company 1');


If I attempt to make both users an owner of the company, the statement fails:

INSERT INTO dbo.CompaniesUsers (CompanyID, UserID, CompanyOwner)
VALUES (1, 1, 1)
    , (1, 2, 1);



  Msg 2601, Level 14, State 1, Line 62
  Cannot insert duplicate key row in object 'dbo.CompaniesUsers' with unique index 'IX_CompanyUsers'. The duplicate key value is (1, 1).


However, if I only allow one of the users to be the "owner", then the insert works:

INSERT INTO dbo.CompaniesUsers (CompanyID, UserID, CompanyOwner)
VALUES (1, 1, 1)
    , (1, 2, 0);

SELECT *
FROM dbo.CompaniesUsers


╔═══════════╦════════╦══════════════╗
║ CompanyID ║ UserID ║ CompanyOwner ║
╠═══════════╬════════╬══════════════╣
║         1 ║      1 ║            1 ║
║         1 ║      2 ║            0 ║
╚═══════════╩════════╩══════════════╝


If you need to ensure a "user" can own at most a single "company", then this unique, non-clustered index, should do the trick:

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers_1
ON dbo.CompaniesUsers(UserID, CompanyOwner)
INCLUDE (CompanyID)
WHERE CompanyOwner = 1;


I originally missed the requirement that a "user" can only be a member of a single "company".  With that in mind, you'd need to add this index (the "IX_CompanyUsers_1" index would be unnecessary):

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers_2
ON dbo.CompaniesUsers(UserID)