Re: How to prevent updates on foreign key default values

Christoffer,

The problem here is that, by your business definition, the relationship between students and schools is optional -- a student may be entered without a school. Yet you are defining the relationship in the database as required and this is forcing the workaround of adding a bogus entry into the schools table (which might be better named "schools_and_misc_codes" as a result).

Instead:

CREATE TABLE school (
  schoolid INT NOT NULL PRIMARY KEY,
  schoolname VARCHAR(100) NOT NULL);

CREATE TABLE student (
  studentid INT NOT NULL PRIMARY KEY,
  studentname VARCHAR(100) NOT NULL);

CREATE TABLE student_school (
  studentid INT NOT NULL PRIMARY KEY

      REFERENCES student (studentid),
  schoolid INT NOT NULL

      REFERENCES school (schoolid));

"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:21:18 CEST