Re: composite foreign key one field constant?

Add the TYPE column to the WORKDETAILS table as NOT NULL DEFAULT 'template-type'. That will allow you to properly define the foreign key. Then define a view over the WORKDETAILS table that omits the TYPE column.

BTW, I have worked with this implementation before and in the long run it's a real bastard. Looks like you are trying to avoid multiple code tables by merging them into one and adding a type column. Don't do it. Use multiple tables. Otherwise, this "simplification" will cost you tons of complicated and buggy SQL down the road.

"Don Dwoske" <ddwoske_at_yahoo.com> wrote in message news:32001f08.0211190852.c93f28f_at_posting.google.com...
> 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
Received on Wed Nov 20 2002 - 08:41:01 CST