CODEWITHSUNDEEP
sql-servert-sql
akwintermute
akwintermute

Reputation: 123

Enforce additional constraints on N:M table server

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?

Upvotes: 1

Views: 60

Answers (2)

Charlieface
Charlieface

Reputation: 71364

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

Upvotes: 2

Clay
Clay

Reputation: 5084

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.

Upvotes: 1

Related Questions