It's been a while since I've blogged something. I've just been busy!
It will actually use a UDF and check constraints to mimic a foreign key constraint, but it will enforce referential integrity against more than one table.
Some of my developers need to save records to a table from values retrieved from and referenced by one other table. This is, of course, nothing more than a foreign key constraint. Simple enough.
Now, they need to save two more special-case values, but cannot add them to the reference table. You'll see why in a minute.
(Explaining the real scenario would require getting you bogged down in far more details than what I actually implemented, so please allow me to contrive a story for the purposes of greatly shortening things.)
Users work for a company that makes combination locks for toddlers. (I have a 16-month old, so I guess that's part of where I got the idea from. Roll with me, here.) They have a search dialog box. In the search dialog, they have select lists for a weird combination lock with colors as the combination. The lock has 3 positions. For example, a lock could have red-green-blue.
The current search dialog looks for customers with specific color combinations in those three positions.
For business reasons, you have to save each search to a separate table, called LockSearchHistory. That LockSearchHistory has foreign key constraints against a table called ColorList. There are three FK constraints, one for each position.
Now, the company has invented a new lock with 4 positions: Example: orange-blue-yellow-black.
And now, they want to modify the search dialog for all customers, regardless of if it's a 3 position or 4 position lock, and users want to specify the position of only the colors they know.
In other words, for a 3-position lock, they want to search for: (any color)-green-blue-(no color).
For a 4-position lock, they want to search for: orange-(any color)-(any color)-black.
You still have to save each search to the LockSearchHistory table.
You definitely don't want to add "any color" and "no color" values to your ColorList table, because then users might accidentally enter them when adding new customer locks. I suppose you could add "any" and "none" with some kind of flags, but that requires more application and schema changes than just a search dialog box should need. (There will be application and schema changes, just smart ones.)
Here's (what I think is) an elegant solution, step-by-step.
1. Delete the three current foreign key constraints, one for each position:
if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[FK_LockSearchHistory_PositionOne]') and parent_object_id = object_id(N'[dbo].[LockSearchHistory]'))
alter table LockSearchHistory drop constraint FK_LockSearchHistory_PositionOne
go
if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[FK_LockSearchHistory_PositionTwo]') and parent_object_id = object_id(N'[dbo].[LockSearchHistory]'))
alter table LockSearchHistory drop constraint FK_LockSearchHistory_PositionTwo
go
if exists (select * from sys.foreign_keys where object_id = object_id(N'[dbo].[FK_LockSearchHistory_PositionThree]') and parent_object_id = object_id(N'[dbo].[LockSearchHistory]'))
alter table LockSearchHistory drop constraint FK_LockSearchHistory_PositionThree
go
2. Add a new column to the LockSearchHistory table, say, called "PositionFour".
3. Create a new table, "ListNoneAny," with "*no color*" and "*any color*" values.
4. Create a user-defined function (UDF), called "InColorListOrListNoneAnyFN":
create function dbo.InColorListOrListNoneAnyFN (@strColor varchar(50))
returns bit as
begin
declare @bitReturn bit=0
if exists(
select 1 from ColorList where PositionOne=@strColor
union
select 1 from ColorList where PositionTwo=@strColor
union
select 1 from ColorList where PositionThree=@strColor
union
select 1 from ColorList where PositionFour=@strColor
union
select 1 from ListNoneAny where Title=@strColor
) set @bitReturn=1
return @bitReturn
end
5. Add four check constraints, one for each position:
alter table LockSearchHistory with nocheck
add constraint CK_LockSearchHistory_PositionOne check ((dbo.InColorListOrListNoneAnyFN(PositionOne))=1)
go
alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionOne
go
alter table LockSearchHistory with nocheck
add constraint CK_LockSearchHistory_PositionTwo check ((dbo.InColorListOrListNoneAnyFN(PositionTwo))=1)
go
alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionTwo
go
alter table LockSearchHistory with nocheck
add constraint CK_LockSearchHistory_PositionThree check ((dbo.InColorListOrListNoneAnyFN(PositionThree))=1)
go
alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionThree
go
alter table LockSearchHistory with nocheck
add constraint CK_LockSearchHistory_PositionFour check ((dbo.InColorListOrListNoneAnyFN(PositionFour))=1)
go
alter table LockSearchHistory check constraint CK_LockSearchHistory_PositionFour
go
Now, you effectively have a foreign key constraint that references more than one table.
In closing, when tweeting #sqlhelp about my problem, Brent Ozar himself suggested using indexed views. But, indexed views don't allow unions. Because of this, I think he assumed that the view would use a join. So, the only way was with a UDF and check constraints. I am only saying this because I googled a ton, and found several suggestions and examples to use indexed views for this sort of thing.
