Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: composite foreign key one field constant?

Re: composite foreign key one field constant?

From: Martin Doherty <martin.doherty_at_elcaro.moc>
Date: Tue, 19 Nov 2002 17:25:01 -0800
Message-ID: <_zBC9.12$cK2.175@news.oracle.com>


Don,

You didn't specify your version of Oracle, and you can easily check syntax questions by consulting the SQL Reference Manual.

Is 'template-type' the only distinct value in CONTROLLED_VOCABULARY.TYPE column (now and forever)? If so, I agree with Daniel that you can just lose that column entirely since it appears to be adding no information whatsoever.

However, if that column contains (or could contain in future) multiple distinct values and your WORKDETAILS table will only refer to rows in CONTROLLED_VOCABULARY where type = 'template-type', then your WORKDETAILS table *must* include a TYPE column, your FK must specify both columns (TYPE, TEMPLATETYPE) and it is the responsibility of the application which inserts rows into WORKDETAILS to make sure that TYPE is alsways set to 'template-type'. You could reinforce this business rule (as Karsten points out) by applying a CHECK (TYPE='template-type') in your WORKDETAILS table definition. You could even make 'template-type' the default value for WORKDETAILS.TYPE and omit this column from your INSERT statements.

If the distinct values that occur in CONTROLLED_VOCABULARY.VALUE where TYPE = 'template-type' can be *guaranteed never to occur* where TYPE <> 'template-type', then you could consider using a single-column FK that simply links WORKDETAILS.TEMPLATETYPE to CONTROLLED_VOCABULARY.VALUE, but this to me sounds unlikely and pretty dodgy (since it obscures the true relationship). Of course you would also need to make sure an index exists which has VALUE as its leading column for this to even be an option.

Martin Doherty

Daniel Morgan wrote:

>Don Dwoske wrote:
>
>
>
>>Basically, I want to create a foreign key to a table
>>that has a composite primary key of two fields, but
>>in my table, i'm only storing one of the values, because the
>>other one is always constant.
>>
>>First table: contain data that ends up in my
>>GUI pulldowns for various data items. Each type has
>>allowable values.
>>
>>CREATE TABLE CONTROLLED_VOCABULARY (
>> TYPE VARCHAR2(64) NOT NULL,
>> VALUE VARCHAR2(64) NOT NULL,
>> DESCRIPTION VARCHAR2(256)
>> constraint CV_PK PRIMARY KEY (MIMETYPE, VALUE)
>>);
>>
>>INSERT INTO CONTROLLED_VOCABULARY VALUES
>> ( 'template-type', 'templateOne', 'some desc');
>>INSERT INTO CONTROLLED_VOCABULARY VALUES
>> ( 'template-type', 'templateTwo', 'some desc');
>>
>>Other table (WORKDETAILS) I have a
>>column TEMPLATETYPE that I want to reference the value
>>of the first table where the TYPE='template-type'
>>
>>alter table WORKDETAILS add
>> constraint WORKDETAILS_TEMPLATE_FK
>> foreign key ('template-type', TEMPLATETYPE)
>> references CONTROLLED_VOCABULARY (type, value) ;
>>
>>This was not allowed... i also tried using a check to do a select
>>from controlled_vocabulary where type='...' but that didn't
>>work either...
>>
>>Is this possible.. syntax? do i have to use a trigger/proc? i
>>don't want to have to store 'template-type' in one of my fields
>>because it's always the same value.
>>
>>Cheers,
>>-Don
>>
>>
>
>Perhaps I am misunderstanding ... but if "i'm only storing one of the
>values, because the other one is always constant." ... the "other one"
>doesn't belong in the primary key and likely doesn't belong in the
>table.
>
>Please clarify.
>
>Daniel Morgan
>
>
>
Received on Tue Nov 19 2002 - 19:25:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US