Unique Constraint on 2 columns or single column and null exists

Question

Within a multi-tenant database we have a table that contains some system values and some tenant values defined as follows.

CREATE TABLE [dbo].[ItemTypes] (
    [Id]         INT          IDENTITY PRIMARY KEY
  , [TenantId]   INT          NULL
  , [SystemType] BIT          NOT NULL
  , [Name]       VARCHAR(255) NOT NULL
  , CONSTRAINT [CK_SYSTEM_TENANT_TYPE] CHECK ((
                                                 [TenantId] IS NULL
                                                  AND [SystemType] = 1
                                             )
                                           OR (
                                               [TenantId] IS NOT NULL
                                                  AND [SystemType] = 0
                                           )
    )
);

ALTER TABLE [dbo].[ItemTypes]
ADD CONSTRAINT [UQ_TenantId_Name]
    UNIQUE NONCLUSTERED ([TenantId], [Name]);

So an ItemType can either be for all tenants or a single tenant. We have a unique index to ensure that a tenant cannot define two identical ItemTypes.Name.

We also need to ensure that if an ItemType is a SystemType no Tenant can create the same ItemTypes.Name.

Update to Clarify:
For example if we have a System ItemType.Name of 'foo' then no Tenant can create an ItemType.Name of 'foo'. BUT if there is no System ItemType.Name of 'bar' then every tenant can have its own ItemType.Name of 'bar'

What is the best possible way to do this?

Answer 1

If I understood the requirements correctly, it should be possible to resolve the issue using DDL only. My understanding is that, in addition to the already existing uniqueness constraint on Name per tenant/system, a system type name should not be used as a tenant type name and vice versa.

You can implement the solution with the help of a materialised (indexed) view.

First define the view like this:

CREATE VIEW dbo.ItemTypesNameConstraint
WITH SCHEMABINDING
AS 
SELECT
  it.SystemType
, it.Name
, COUNT_BIG(*) AS BigCount
FROM
  dbo.ItemTypes AS it
GROUP BY
  it.SystemType
, it.Name
;

The view will return unique combinations of SystemType, Name. This way, if a name is used both by the system and by a tenant, it will occur twice in the output. That is the situation we want to prevent. In order to do that, as the second and last step of the implementation, create a unique index on the view's Name column:

CREATE UNIQUE CLUSTERED INDEX
  UQ_ItemTypesNameConstraint
ON
  dbo.ItemTypesNameConstraint (Name ASC)
;

Now any attempt to re-use a system type name for a tenant type – or a tenant type name for a system type, for that matter – will result in an error. The only minor issue is that the error might not be very clear for someone unfamiliar with the method (or even for yourself after a few months). You could try and alleviate that somewhat by choosing the name for either the view or the unique index that could make things clearer.

You can play with the solution at dbfiddle.uk.

Answer 2

I don't think this can be done purely with constraints given your current table design.

You would need a trigger to enforce this.

CREATE OR ALTER TRIGGER dbo.OneSystemItem
ON dbo.ItemTypes
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM inserted i
    JOIN dbo.ItemTypes it ON it.Name = i.Name
      AND NOT EXISTS (SELECT i.TenantId INTERSECT SELECT it.TenantId)  -- nullable compare
      AND (i.TenantId IS NULL OR it.TenantId IS NULL)
  )
    THROW 50001, N'Only one system type allowed', 0;

db<>fiddle

I note that SystemType does not encode any new information that you do not already have from the NULL in TenantId. Therefore it should be removed. At the most you can create it as a computed column

SystemType AS CASE WHEN TenantId IS NULL THEN CAST(1 AS bit) ELSE CAST(0 as bit) END

---

With a different design you could enforce this purely with constraints

CREATE TABLE dbo.ItemTypes (
  , Name       VARCHAR(255) NOT NULL PRIMARY KEY
  , SystemType BIT          NOT NULL
  , UNIQUE (Name, SystemType)
);

CREATE TABLE dbo.Tenant_Type (
    Name       VARCHAR(255) NOT NULL
  , SystemType AS CAST(0 AS bit) PERSISTED
  , TenantId   INT          NOT NULL
  , PRIMARY KEY (Name, TenantId)
  , FOREIGN KEY (Name, SystemType) REFERENCES dbo.ItemTypes (Name, SystemType)
);