Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: How to prevent updates on foreign key default values

Re: How to prevent updates on foreign key default values

From: Jan Emil Larsen <jel_at_g-it.dk>
Date: Thu, 3 Jan 2002 22:18:23 +0100
Message-ID: <3c34ca96$0$89111$edfadb0f@dspool01.news.tele.dk>


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>
> 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
Received on Thu Jan 03 2002 - 15:18:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US