Unique Constraint on 2 columns or single column and null exists

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?

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


StackExchange Database Administrators Q#317571, answer score: 4