Re: composite foreign key one field constant?

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 - 18:01:55 CST