I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views. Although doable is there another solution I am not seeing? I have scoured the web in search of an answer knowing I cannot be the only SQL developer in this situation. I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.
CREATE TABLE dbo.CodesTable (
PartyGrpId INT NOT NULL
, CdTyp VARCHAR ( 8 ) NOT NULL
, CompId INT NOT NULL
, CdValue VARCHAR ( 8 ) NOT NULL
, CdValueDesc VARCHAR ( 255 ) NULL
, AltValueDesc VARCHAR ( 100 ) NULL
, DefaultInd CHAR ( 1 ) NULL
, OrderNum SMALLINT NULL
, ActiveCd CHAR ( 1 ) NULL
, ExpireDtm SMALLDATETIME NULL
, EffectDtm SMALLDATETIME NULL
, ModById INT NULL
, ModDtm SMALLDATETIME NULL
, CreateById INT NULL
, CreateDtm SMALLDATETIME NULL
, CONSTRAINT PC_dbo_EcdDetail
PRIMARY KEY CLUSTERED ( PartyGrpId ASC, CdTyp ASC, CompId ASC, CdValue ASC )
ON FG_Data
) ON FG_Data;
I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this. :)
ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'S