1

I have 2 tables in SQL database, User and Company.

User belongs to one company.

So user table has UserID & CompanyID (foreign key)

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

So company table has CompanyID & 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?

1
  • 1
    If you are using a specific DBMS it would be helpful to tag this question with it.
    – Hannah Vernon
    Feb 23, 2018 at 17:16

1 Answer 1

2

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)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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