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
Let me see if I understand. You want to ensure that WORKDETAILS only has
a subset of the possible TYPEs in CONTROLLED_VOCABULARY? The purpose of
the FK is to make sure there's a parent PK. To allow only a subset, do
you need to use a CHECK constraint instead of (in addition to) a FK
constraint?
Received on Tue Nov 19 2002 - 19:11:16 CST
