Re: Composite Referential Integrity Constraint

"Brian E Dick" <bdick_at_cox.net> wrote in message news:<8aTJ9.2855$0b.106702_at_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_at_yahoo.com> wrote in message
> news:8882aa3c.0212111350.7df40185_at_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. Received on Thu Dec 12 2002 - 09:52:33 CST