External key relationship with condition

Good morning,

I have a main table with id and type. Depending on the type, I have tables for children that use this id as a foreign key to ensure consistency. For example. for the main table:

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

      

For a child table named Child_A that stores additional data for type A;

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

      

How can I prevent the type in my main table from changing to a different value when there is a matching record in my child table. My referential integrity is currently preserved, but it makes no sense to store in the information Child_A of type A, then the record in the main table is of a different type.

change:

Will the only key be a foreign key with two attributes (id and type) and a duplicate type in each child table? For example. for table child_A;

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

      

Hope this is clear enough.

Sylvain

+3


source to share


1 answer


You can create a check constraint that uses a custom function to determine if an identifier value is contained in a matching 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

      

+1


source







All Articles