Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!news.compuserve.com!news-master.compuserve.com!not-for-mail
From: MSherrill@compuserve.com
Newsgroups: microsoft.public.sqlserver.programming,comp.databases.ms-sqlserver,comp.databases.theory
Subject: Re: How to prevent updates on foreign key default values
Date: Wed, 10 Oct 2001 05:22:14 GMT
Organization: CompuServe Interactive Services
Lines: 54
Message-ID: <3bc3da86.56994633@news.compuserve.com>
References: <ur8sfxxk7.fsf@online.no>
NNTP-Posting-Host: sfr-tgn-yyi-vty44.as.wcom.net
X-Trace: suaar1aa.prod.compuserve.com 1002691632 27543 216.192.17.44 (10 Oct 2001 05:27:12 GMT)
X-Complaints-To: newsmaster@compuserve.com
NNTP-Posting-Date: 10 Oct 2001 05:27:12 GMT
X-Newsreader: Forte Free Agent 1.21/32.243
Xref: easynews microsoft.public.sqlserver.programming:170408 comp.databases.ms-sqlserver:59567 comp.databases.theory:18634
X-Received-Date: Wed, 10 Oct 2001 02:32:05 MST (news.easynews.com)

On 07 Oct 2001 17:46:32 +0200, Christoffer Vig <chrde@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