Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<fmhfvukg4tek842okavhkvk6vukl4qmanv_at_4ax.com>...
> On 11 Dec 2002 13:50:15 -0800, andyho99_at_yahoo.com (Andrew) wrote:
>
> >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.
>
>
> I wouldn't say that. Your design looks lousy (because the cd_grp
> column seems to be a constant) so you may be better of by redesigning
> the master_cd table.
> Also I don't share your concern about 'wasting' 4 bytes per record.
> You would rather be concerned about normalization.
>
> Regards
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Well, master_cd table is designed as master look up table. The
combination of cd_grp and cd_name make a unique key. For example, this
table may contains cd_grp - 'COUNTRY' and cd_name - 'US'. My point is
if I can avoid using *_cd_grp in each application table that need to
refer master_cd tables (foreign key).
Received on Wed Dec 11 2002 - 21:56:08 CST
