CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designcheck-constraints
SSISPissesMeOff
SSISPissesMeOff

Reputation: 450

How to set up an extra constraint for 1-to-many relationship without using triggers

I have come across a database in my current position that needs allot of TLC to say the least, there are many cross reference tables all having the same structure schema wise, and all using triggers to enforce business logic, or domain integrity it would seem. I am trying to figure out if I can replace some of the triggers with constraints. Below is a typical table definition meeting this pattern. There are about 400 of these tables that exist all using the same trigger based solution.

CREATE TABLE dbo.FooXRef
(
FooXRefID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
BarID INT NOT NULL CONSTRAINT [fk_Bar] FOREIGN KEY REFERENCES dbo.Bar(BarID),
DefaultRecord BIT NOT NULL CONSTRAINT [DF_FooXRef_Default] DEFAULT(1)
);

The rule being there can exist infinite number of records with the same value in BarID and DefaultRecord = 0, but only a single record can exist where BarID is in the table and DefaultRecord = 1.

What type of constraint would I need to setup to allow for that combination?

This table structure and trigger to enforce this combination is 400 in the current database, and in the next one I am undertaking in the thousands. Is it simply not possible to setup a constraint that would work for this case?

Upvotes: 1

Views: 66

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

Here is one possible variant without using a trigger.

Create a filtered unique index on (BarID, DefaultRecord) with the filter condition WHERE ([DefaultRecord]=(1)).

With such index in place you can insert only one row per BarID that has DefaultRecord = 1.

It is possible to have zero number of rows that have DefaultRecord = 1.

It is possible to have any number of rows that have DefaultRecord = 0.

CREATE UNIQUE NONCLUSTERED INDEX [IX_DefaultRecord] ON [dbo].[FooXRef]
(
    [BarID] ASC,
    [DefaultRecord] ASC
)
WHERE ([DefaultRecord]=(1))

Upvotes: 2

Related Questions