How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?

Question

I have a foreign key linking INSTRUMENT.VENDOR to VENDOR.NAME.

Unfortunately the program where users enter information is attempting to insert into INSTRUMENT with a value of 0 for vendor. I need to intercept the insert and change the value to NULL if it is 0.

The only way I know how to do this is to create an INSTEAD OF INSERT trigger on INSTRUMENT. But if I do this, I believe I have to handle all fields in the trigger, and if at any point a new field is added to INSTRUMENT the trigger will need to be updated or that field will never have a value inserted.

Is there a better way to do this that I'm missing?

Answer 1

The most concordant solution would be to change the front-end application so that, in the first place, it inserts Null instead of 0.

If this isn't possible, it might be relevant to explain why as part of the question, to understand exactly what the profile of limitations are on a solution.

As @SergeyA notes in the comments, it may be a possibility to have a zero-keyed entry in the master table which is used to represent a default or absent link whilst maintaining the referential integrity between the tables.

If you must fall back to triggers, in my view it would be worth considering the possibility of using the trigger to enforce the foreign key selectively, than to use the trigger to rewrite the data on the fly. That is, you would disable the built-in foreign key check, then replace it with a trigger that only enforces the foreign key if it is non-zero, and ignores the enforcement if it is zero.

It is very easy to abuse triggers in ways that make subsequent supervision or re-adaptation of the database extremely difficult and error-prone.

Anything that alters the behaviour of basic SQL commands (such as rewriting an insert zero into an insert null, on the fly), or adds side-effects or forms of reactivity which are not limited to the enforcement of constraints, and does it all in a way that must be completely transparent to a front-end module that cannot be changed, are really the kind of things that are done when an application is already heavily sclerotic and terminally-ill.

Answer 2

The only way I know how to do this is to create an INSTEAD OF INSERT trigger on INSTRUMENT. But if I do this, I believe I have to handle all fields in the trigger, and if at any point a new field is added to INSTRUMENT the trigger will need to be updated or that field will never have a value inserted.

This is not necessarily correct.

You'd only have to handle the fields you care about and the ones which are required by the table. If a new nullable field, that you don't care about, was added to the table later on, then that doesn't affect your trigger at all. (Obviously, that new field won't receive data from the source though.)

Answer 3

If you are controlling the SQL for the insert, the function Nullif(FieldName,0) is a friend , provided that 0 is not a valid ID for an instrument