Re: How to prevent updates on foreign key default values

Christoffer Vig (chrde_at_online.no) writes:
> I seem to have stumbled upon a general problem with implementing default
> values for foreignkeys. I work on MSsqlserver 7.0. If you set a default
> value on a foreign key column, you cannot expect other value(s) stored in
> the row on the referencing table forever will contain what you wanted as
> default values. The best solution I have come up with involves using a
> trigger to prevent update on the row referenced as default .
> (Referential integrity makes sure deletes are not allowed
> ). However, I find my solution rather messy.

A default constraint on a foreign key does not strike me as a very good idea. It would possibly be OK if the foreign key refers to a a pre-loaded table, that is a table into which data is loaded when you build an empty database. But if the foreign key refers to a with user-entered data, you have probably gone lost somewhere in your design.

> Now what happens if the first thing I do in this database is
> register a new student? ( ie. no school has yet been registered)
>
> I am told that there is a foreign key preventing this insert.
> Good.
> In the school table I insert 'NOT REGISTERED'.
> It is automatically assigned a schoolid of 1, which makes this the
> default school for all students.

As Ivan Arjentisnki said: use NULL for this. You say you don't want to use NULL, but this is precisely why you have NULLs. NULL means "not applicable", "unknown", "not known", and this appears to be exactly the case here.  

> create trigger trigger_school_default on school
> for update
> as
> if _at__at_rowcount > 1 --
> begin
> raiserror ('Multirow updates not allowed on school table', 16, 1)
> rollback transaction
> return
> end
>
> if (select schoolid from deleted) = 1
> begin
> raiserror ('go away! do not destroy my default. (moron)', 16, 1)
> rollback transaction
> end

There is no reason to disallow multi-row updates. Write the test as:

   IF EXISTS (SELECT * FROM deleted WHERE schoolid = 1)

And the trigger should be FOR DELETE as well.  

> But I don't really like this solution. Are there other solutions?

I see it as a sign of health that you don't like the solution. NULL is the way to go.

-- 
Erland Sommarskog, Abaris AB
sommar_at_algonet.se
SQL Server MVP