Re: Composite Referential Integrity Constraint

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 Received on Wed Dec 11 2002 - 17:17:02 CST