Enforce additional constraints on N:M table server

Question

I have the following tables in SQL Server 2019, where each footprint can have many stencils and each stencil can have many footprints.

CREATE TABLE dbo.Footprint 
( 
    FootprintId INT IDENTITY(1,1) NOT NULL 
        CONSTRAINT PK_Footprint PRIMARY KEY CLUSTERED, 
    FootprintName NVARCHAR(255) NOT NULL 
        CONSTRAINT U_FootprintName UNIQUE
);

CREATE TABLE dbo.Stencil 
( 
    StencilId INT IDENTITY(1,1) NOT NULL 
        CONSTRAINT PK_Stencil PRIMARY KEY CLUSTERED, 
    StencilName NVARCHAR(255) NOT NULL 
        CONSTRAINT U_Stencil UNIQUE (StencilName),
    UseLocation NVARCHAR(255) NOT NULL,
    PartNumber NVARCHAR(255) NULL
);

CREATE TABLE dbo.FootprintStencil 
(
    FootprintId INT NOT NULL,
    StencilId INT NOT NULL, 
    CONSTRAINT PK_FootprintStencil 
        PRIMARY KEY CLUSTERED (FootprintId, StencilId),
    CONSTRAINT FK_FootprintStencil_Footprint 
        FOREIGN KEY (FootprintId) REFERENCES dbo.Footprint (FootprintId),
    CONSTRAINT FK_FootprintStencil_Stencil 
        FOREIGN KEY (StencilId) REFERENCES dbo.Stencil (StencilId)
);

Is there any way enforce in the database the constraint that (Footprint.FootprintId, Stencil.UseLocation, Stencil.PartNumber) must be unique as a set in the FootprintStencil relationship?

I could probably do it with a trigger, but is there a better way?

Charlieface · Accepted Answer · 2021-06-02T21:38:50.190

You can enforce this with an indexed view. This trick is similar to this post, but in this case we just want to enforce the uniqueness:

CREATE OR ALTER VIEW dbo.vwFootprint_StencilLocationPart
WITH SCHEMABINDING
AS
SELECT fs.FootprintId, s.UseLocation, s.PartNumber
FROM dbo.FootprintStencil
JOIN dbo.Stencil s ON s.StencilId = fs.StencilId;

GO

CREATE UNIQUE CLUSTERED INDEX IX_vwFootprint_StencilLocationPart
    ON dbo.vwFootprint_StencilLocationPart (FootprintId, UseLocation, PartNumber);

GO

You now have a multi-table constraint. The server will automatically maintain it whenever there are changes to the underlying tables, and throw any uniqueness violations.

Note: Indexed views have restrictions, among them:

The view must be schema-bound, and prevents changes to underlying columns
Two-part names only,
Values must be deterministic
Only INNER JOIN or CROSS JOIN is allowed, not LEFT/RIGHT/APPLY, subqueries or CTEs

So simple, why didn't I think of it :). Do you think dropping dbo.Footprint from the join would make any difference, since all that is being used is the FootprintId which is already part of dbo.FootprintStencil? — akwintermute, Jun 02 '21 at 21:20
Hum good point, yes you could. I must say, something feels not quite right about your design that I can't quite put my finger on. Maybe you want to rethink the relationships. Perhaps `UseLocation, PartNumber` can be a unique key anyway? — Charlieface, Jun 02 '21 at 21:38

Clay · Answer 2 · 2021-06-02T19:43:01.050

I've done that kind of thing with check constraints that refer to scalar functions:

In FootPrintStencil, I'd add the constraint:

....
constraint [CheckExtendedRequirements] 
  check ( dbo.ExtendedRequirements( FootprintId, StencilId ) = 1 )
...

where dbo.ExtendedRequirements would be something like:

create function dbo.ExtendedRequirements( @footprintId int, @stencilId int) 
returns bit as
begin
  declare 
    @useLocation nvarchar(255), 
    @partNumber nvarchar( 255 )

  select 
    @useLocation = UseLocation, 
    @partNumber = PartNumber
  from
    dbo.Stencil
  where
    StencilId = @stencilId

  return
  (
    select case when count(*) > 1 then 0 else 1 
    from
      dbo.Footprint f 
      cross join 
      dbo.Stencil s
    where 
      f.FootprintId !=  @footprintId and 
      s.StencilId != @stencilId and
      s.UseLocation = @useLocation and
      s.PartNumber = @partNumber
  )
end

Stream of consciousness and untested...but something like that.

Thank you for the answer, I'll definitely keep it mind for future use cases. The only downside I found that is solved by @Charlieface is that the constraint isn't enforced if a stencil gets updated after it has already been linked to a footprint only when the relationship is initially established. — akwintermute, Jun 02 '21 at 21:27

Enforce additional constraints on N:M table server

2 Answers2