How to prevent updates on foreign key default values

Greetings sql programmers!

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.
If I had not normalised this table it would have been easy to maintain full control on the default values.

As an example consider the following database:

CREATE TABLE school
( schoolid INT IDENTITY PRIMARY KEY,

   schoolname VARCHAR (100) NOT NULL
)

CREATE TABLE student
( studentid INT IDENTITY PRIMARY KEY,

   schoolid INT NOT NULL DEFAULT 1 REFERENCES school(schoolid),    studentname VARCHAR (100) NOT NULL
)

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.

I start registering students, and some schools as well, even assigning different schools to some students.
Now other users start using my magnificent studentdatabase. Some of them do not like the nasty school called 'NOT REGISTERED' That is no school at all they say.
update school set schoolname = 'School of DRI' where schoolid = 1

It seems that it has to be the responsibility of the client-application to make sure 'NOT REGISTERED' is not changed. I don't like that. To prevent messing around with my defaults I write a trigger to prevent update on the school table if schoolid=1. like this

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

Now it works.
But I don't really like this solution. Are there other solutions? Is this a limitation in MSSQLserver? Or is it just me?

databasic regards
Christoffer Vig   Received on Sun Oct 07 2001 - 17:46:32 CEST