Foreign key relationships with a condition

Question

Good morning,

I have a master table with an ID and a type. Depending of the type, i have children tables using this ID as a foreign key to ensure integrity. Eg. for the master table:

master_ID, type
11, A
12, B
13, A

For the child table named Child_A, which stores additional data for type A ;

Child_A_ID, FK_master_ID, ....
1, 11, ....
2, 13, ....

How can I prevent the type in my master table to be changed to a different value when there is a corresponding record in my child table. My referential integrity is currently kept but it has no sense to store in the Child_A information of type A while, the record in the master table is of different type.

edit:

Would having a foreign key with the 2 attributes (ID and type) and repeating the type in each child tables be the only solution? Eg. for the child_A table;

Child_A_ID, FK_master_ID, type, ....
1, 11, A, ....
2, 13, A, ....

Hope it's clear enough.

Answer 1

You can create a check constraint that uses a user defined function to determine if the id value is contained in the relevant type table.

ALTER TABLE MasterTable
  ADD CONSTRAINT CHK_MasterTable_Type
  CHECK(dbo.fn_check_IdBelongsToType(master_ID, type) = 1)

and in the function itself you do something like this:

CREATE FUNCTION fn_check_IdBelongsToType (
    @master_ID int, 
    @type char(1)
)
RETURNS int
AS
BEGIN
   IF @Type = 'A' AND EXISTS (
       SELECT 1
       FROM Child_A
       WHERE FK_master_ID = @master_ID 
   ) RETURN 1

   IF @Type = 'B' AND EXISTS (
       SELECT 1
       FROM Child_B
       WHERE FK_master_ID = @master_ID 
   ) RETURN 1

   IF @Type = 'C' AND EXISTS (
       SELECT 1
       FROM Child_C
       WHERE FK_master_ID = @master_ID 
   ) RETURN 1

   -- after testing all child tables, return 0 to indicate that the value was not found
   RETURN 0

END