Re: Part of Primary Key or New Constraint

"mike" <hillmw_at_charter.net> wrote in message news:1111005552.276908.76790_at_g14g2000cwa.googlegroups.com...
> I have the li_struct defined like:
>
> ALTER TABLE LI_STRUCT
> ADD CONSTRAINT XFK_LI_STRUCT_PARENT FOREIGN KEY (PAR_STRUCT_ID)
> REFERENCES LI_STRUCT_DEF(STRUCT_DEF_ID)
>
> ALTER TABLE LI_STRUCT
> ADD CONSTRAINT XFK_LI_STRUCT_CHILD FOREIGN KEY (CHD_STRUCT_ID)
> REFERENCES LI_STRUCT_DEF(STRUCT_DEF_ID)
>
> ALTER TABLE LI_STRUCT
> ADD CONSTRAINT XPK_STRUCT_IDX PRIMARY KEY (STRUCT_ID)
>
> CREATE TABLE LI_STRUCT
> (
> STRUCT_ID INTEGER NOT NULL,
> PAR_STRUCT_ID INTEGER NOT NULL,
> CHD_STRUCT_ID INTEGER NOT NULL,
> STRUCT_LEVEL INTEGER NOT NULL,
> STATUS CHAR(15) NOT NULL
> )
>
> This says my struct_id is my primary key. It is assigned by a sequence.
>
> PAR_STRUCT_ID and CHD_STRUCT_ID both need to be defined in the table
> LI_STRUCT_DEF.
>
> Question I have is I need PAR_STRUCT_ID and CHD_STRUCT_ID both uniquely
> defined as well, so I need a constraint.
>
> Do I make them part of the primary key?
>
> Mike
>

This has homework written all over it, but...

The answer is "it depends". You are working backwards. A primary key is decided by the semantics of the data, not other constraints on the data. The answer is in the answer to this question: What is the sound of one hand clapping? Just kidding. The question is, "Which column(s) always uniquely identifies all of the other columns?" Also, is the combination of the values in these columns unique, or just each column? Depending, you may wind up making some part of the PK, or just using a UNIQUE constraint on one or more together. Received on Wed Mar 16 2005 - 19:35:35 CST