Re: How to prevent updates on foreign key default values
Date: Thu, 3 Jan 2002 22:18:23 +0100
Just jumping in to express that I totally agree with Mikes presentation!
A student (in this context) is a person with a relationship to a school. Any spurious doubt if NULL is "not present", "not applicable" or "not known" vanishes, and so does all the troubles with 3VL.
<MSherrill_at_compuserve.com> skrev i en meddelelse
news:3bc3da86.56994633_at_news.compuserve.com...> On 07 Oct 2001 17:46:32
+0200, Christoffer Vig <chrde_at_online.no>
> >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.
> >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
Received on Thu Jan 03 2002 - 22:18:23 CET