Pre-checking foreign key constraints and default value -- best way?

Question

Pre-checking foreign key constraints and default value -- best way?

pkeefe

SSC Enthusiast

Points: 107
More actions
October 3, 2005 at 2:56 pm

#91452

I am looking for suggestions on the best way to implement a default value when a foreign key constraint would fail...
To clarify: I am supporting a fairly complex system where we want to implement a default value if the value to be inserted (or updated) is not in the foreign key table. There about 8 to 10 existing stored procedures that touch the table. I think my options are either check the data prior to insert (or update) or to add trigger.
Thanks in advance for your help and time,
Pete

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110192 More actions · Answer 1

Where the value you trying to insert comes from?

Application should not invent values in columns referenced by FK, it must retrieve it from DB. That's the only point of FK.

If there is any chance application will insert value not existing in referenced PK there is a huge hole in logic!

_____________
Code for TallyGenerator

pkeefe SSC Enthusiast Points: 107 More actions · Answer 2

Sergiy:

The problem shows up because there are three levels of databases in a hierarchy of many to one. Someone may have deleted a value from a lookup table in a higher level db and this change does not get to bottom level before an update is create that goes up the chain trying to insert a new record with the deleted value.

Pete

Sergiy SSC Guru Points: 110192 More actions · Answer 3

First, you better set up trigger to delete references on all levels to just deleted values on the top level. Otherwise your data does not make any sence.

Second, when you do update just retrieve FK value from LEFT JOIN to PK table on PK_ID = ID_YOU_TRYING_TO_Insert. It will replace all not existing anymore values with NULL.

_____________
Code for TallyGenerator

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 4

You could change all your inserts to this form:

insert into target (...)
select ...,
   isnull(fk1.value1, FK1_DEFAULT),
   isnull(fk2.value2, FK2_DEFAULT),
   ...
from source s
left join otherdb..lookup1 fk1 on s.value1 = fk1.value1
left join otherdb..lookup2 fk2 on s.value2 = fk2.value2

Or create an INSTEAD OF INSERT trigger that does the same thing using "inserted" for the source.

I agree with Sergiy that allowing the lookup table deletes is a huge data integrity problem. But you probably don't need us to tell you that.