Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: andyho99@yahoo.com (Andrew)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Composite Referential Integrity Constraint
Date: 12 Dec 2002 07:52:33 -0800
Organization: http://groups.google.com/
Lines: 45
Message-ID: <8882aa3c.0212120752.3dfd926@posting.google.com>
References: <8882aa3c.0212111350.7df40185@posting.google.com> <8aTJ9.2855$0b.106702@news2.east.cox.net>
NNTP-Posting-Host: 161.114.1.185
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1039708353 1836 127.0.0.1 (12 Dec 2002 15:52:33 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 12 Dec 2002 15:52:33 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.misc:90472
X-Received-Date: Thu, 12 Dec 2002 08:52:09 MST (news.easynews.com)

"Brian E Dick" <bdick@cox.net> wrote in message news:<8aTJ9.2855$0b.106702@news2.east.cox.net>...
> There was a recent thread similar to this subject. Do a Google search on
> "composite foreign key one field constant".
> 
> "Andrew" <andyho99@yahoo.com> wrote in message
> news:8882aa3c.0212111350.7df40185@posting.google.com...
> > I was wondering whether the parameter of FOREIGN KEY keyword could be
> > hardcoded instead of column name. For example:
> >
> > change
> >
> > ALTER TABLE employee
> >    ADD CONSTRAINT fk_state_cd
> >    FOREIGN KEY (state_cd_grp, state_cd)
> >    REFERENCES master_cd(cd_grp, cd_name);
> >
> > to
> >
> > ALTER TABLE employee
> >    ADD CONSTRAINT fk_state_cd
> >    FOREIGN KEY ('ST', state_cd)
> >    REFERENCES master_cd(cd_grp, cd_name);
> >
> > In this case, I don't need state_cd_grp column in employee table. I
> > tried the second sql unfortunately, it does NOT work. First sql works,
> > but it will waste space if table growing very big.
> >
> > I think the only thing I can do to avoid state_cd_grp in employee
> > table is to use trigger instead of Referential Constraint. Am I right?
> > Thx.


Sorry, I should have searched the keyword first. I think that I prefer
individual look up table instead of one huge look up table with
code_group. I don't want to repeat the reasons stated in that posting.
The experience of my previous project, they broke down master look up
table to many individual look up tables. One of the reason I know is
that these look tabls owned by different group. Breaking down is
easier to make modification.

For those DBA still not willing to break down to individual look up
table. The only options you have are either putting code_group column
(a constant) for eacg code_name or using trigger instead of foreign
key to enforce the constraint. BTW, I am not DBA here. I wouldn't
design this way.