Geek asks and answers

How can I apply uniqueness rules that include more than one table?

For example, having tables:

Companies (CompanyID)
Workers (WorkerID, CompanyID)
Users (WorkerID, Username)

With the right relationships defined, how can I enforce the predicate in which the username is unique in the company, where is its worker located in ?

+2
source share
3 answers

You can use an indexed view for this.

CREATE VIEW dbo.EnforceConstraint
WITH SCHEMABINDING
AS
SELECT CompanyID, Username
FROM dbo.Users u 
    JOIN dbo.Workers w ON w.WorkerID = u.WorkerID

GO

CREATE UNIQUE CLUSTERED INDEX ix ON dbo.EnforceConstraint(CompanyID, Username)

There are certain options SETthat you must enable to change the underlying tables of indexed views, but if you are on SQL Server 2005+, they are enabled by default.

+3
source

You cannot directly, but you have a couple of options:

  • Users CompanyID.

  • insert/update .

0

You may also consider:

enter image description here

and make sure that it WorkerNois unique within the company:

alter table Workers
add constraint un_co_wrk unique (CompanyID, WorkerNo) ;
0
source

More articles:


All Articles