Re: How to prevent updates on foreign key default values
Date: Sun, 7 Oct 2001 20:50:59 +0200
Message-ID: <OKTA2f1TBHA.1712_at_tkmsftngp03>
Christoffer,
You could allow NULLs in student.schoolid. NULL could mean that you don't
know the school yet for this student.
--
Ivan Arjentinski
----------------------------------------------------------------------
Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
scripts), sample data (INSERT INTO....) and desired result set when asking
for assistance is highly appreciated
----------------------------------------------------------------------
"Christoffer Vig" <chrde_at_online.no> wrote in message
news:ur8sfxxk7.fsf_at_online.no...
>
> 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 - 20:50:59 CEST