CODEWITHSUNDEEP
sqlsql-serverdatabaseforeign-keysrelationship
user1470240
user1470240

Reputation: 716

Add relation with fixed column value

I like to create a 'conditional' (foreign key) relation between 3 tables. In my case, it's like this (of course it's quite more complex, but I've stripped it down to demonstrate the problem situation):

Table [ItemTable]
Column int Id (PK)
Column str ItemName

Table [ItemGroup]
Column int Id (PK)
Column str GroupName

Table [Settings]
Column int Id (PK)
Column str RefersTo ('I' means item, 'G' means item group)
Column int Reference (foreign key depending on 'RefersTo')

The Goal now is to create Relations with contraints like this:

Settings.Reference refers to ItemTable.Id when Settings.RefersTo equals 'I'
Settings.Reference refers to ItemGroup.Id when Settings.RefersTo equals 'G'
No relation in case if RefersTo is empty (so no constraint in this situation)

It sounds like a refer-here-or-there-relation, but I don't know how to achive with MS SQL. I usually use the grafical designer in Management Studio to create and modify table defintion.

Any help is appreciated. Thank you in advance.

Upvotes: 1

Views: 218

Answers (3)

user1470240
user1470240

Reputation: 716

After thinking arround, I came to conclusion to discard the whole idea with one-column-multi-relation thingy.

Answer accepted: Despite on good or bad idea, implementation as desired not possible :)

Thank you all for your answers and comments!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Foreign keys don't have filter clauses in their definition. But you can do this using computed columns:

create table Settings as (
    . . . 
    reference_i as (case when refersto = 'I' then reference end) persisted,
    reference_g as (case when refersto = 'G' then reference end) persisted,
    constraint fk_settings_reference_index
        foreign key (reference_i) references itemTable(id),
    constraint fk_settings_reference_group
        foreign key (reference_g) references groupTable(id)
);

Upvotes: 1

Rigerta
Rigerta

Reputation: 4039

This is not a good design and if you can, it would be better to change it as @VojtěchDohnal already suggested.

If you cannot change it, you could use a trigger after insert, to check if the corresponding value of Reference comes from the correct table, depending on the current value of RefersTo and if not, stop inserting and throw some error, but using triggers is also not the best way performance-wise.

You cannot use an indexed view (which would have been the best, since it would be schema bound and it would get all new values/deleted values from your items or groups) since your sources are two different ones and you would need a union to generate a full list of posible values and there's a limitation that The SELECT statement in the view definition must not contain UNION in indexed views.

The last option: You could use an additional table where you keep all data (Type('I', 'G'), Value (Id's from ItemTable for 'I', Id's from ItemGroup for 'G')) with possible Id's for each table and then make your composite foreign key refer to this new table.

The drawback is that in this case you would need to keep track of changes in both ItemTable and ItemGroup tables and update the newly created table accordingly (for newly inserted values, or deleted values) which is not so nice when it comes to maintenance.

For this last scenario the code would be something like: 

CREATE TABLE ItemTable (Id INT PRIMARY KEY IDENTITY(1,1), ItemName VARCHAR(100))
CREATE TABLE ItemGroup (Id INT PRIMARY KEY IDENTITY(1,1), GroupName VARCHAR(100))
CREATE TABLE Settings (Id INT PRIMARY KEY IDENTITY(1,1), RefersTo CHAR(1), Reference int)

INSERT INTO ItemTable (ItemName) values ('TestItemName1'), ('TestItemName2'), ('TestItemName3'), ('TestItemName4') 
INSERT INTO [ItemGroup] (GroupName) values ('Group1'), ('Group2') 

SELECT * FROM ItemTable
SELECT * FROM ItemGroup
SELECT * FROM Settings

CREATE TABLE ReferenceValues (Type char(1), Val INT, PRIMARY KEY (Type, Val))

INSERT INTO ReferenceValues
SELECT 'I' as Type, i.Id as Val  
FROM  dbo.ItemTable i  
UNION  
SELECT 'G' as Type, g.Id as Val
FROM dbo.ItemGroup as g 

ALTER TABLE dbo.Settings
ADD FOREIGN KEY (RefersTo, Reference) REFERENCES dbo.ReferenceValues(Type, Val);

INSERT INTO Settings (RefersTo, Reference) 
VALUES ('I', 1) -- will work

INSERT INTO Settings (RefersTo, Reference) 
VALUES ('G', 4) -- will not work

Upvotes: 0

Related Questions