Re: How to prevent updates on foreign key default values

On 07 Oct 2001 17:46:32 +0200, Christoffer Vig <chrde_at_online.no> wrote:

>I seem to have stumbled upon a general problem with implementing
>default values for foreignkeys.

I'm not sure what I'm about to say is on point. You've been warned. <g>

[snip]
>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
>)

To me, it looks like the problem in this specific example is that you've blended the attributes of persons and students. (Maybe this applies only to the example, and not to the real problem.)

CREATE TABLE Schools (
  SchoolID INTEGER NOT NULL PRIMARY KEY,   SchoolName VARCHAR(100) NOT NULL
)

CREATE TABLE People (
  PersonID INTEGER NOT NULL PRIMARY KEY,   PersonName VARCHAR(100) NOT NULL
)

CREATE TABLE Students (
  PersonID INTEGER NOT NULL,
  SchoolID INTEGER NOT NULL,

  PRIMARY KEY (PersonID, SchoolID),
  FOREIGN KEY (PersonID) REFERENCES People(PersonID),
  FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)

>Now what happens if the first thing I do in this database is
>register a new student? ( ie. no school has yet been registered)

Register a new student for what? In the absence of a school, that doesn't make sense to me. A student without a school isn't a student.

-- 
Mike Sherrill
Information Management Systems