Re: Using constant as part of reference key

On 17 Mar 2005 04:53:16 -0800, bcg008_at_mot.com (Golan) wrote:

>Let's say I have a master table named "MASTER" with the following
>fields:
>Command_Type number(1),
>Timestamp date,
>a number(3),
>b number(3)
>
>
>and to tables "DETAIL1" & "DETAIL2" with the following fields:
>Timestamp date,
>a number,
>c number,
>d number
>......
>
>
>The primary key for "MASTER" consist of (command_type,timestamp,a) and
>I want to create for bot detail tables a reference key which refers to
>"MASTER" but each table represents a different constant value for the
>command_type field (what I mean is that for example all records in
>DETAIL1 match command_type 1, and all records in DETAIL2 match
>command_type 2. Please notice that commad_type field is not part of
>detail tables).
>What is the syntax for this reference key?

 So, you're trying to represent subtypes of some sort of command entity, which each have different attributes but are each a type of command, by using one-to-one detail tables for each subtype? OK.

 You can't have a constant in a foreign key constraint. You would have to put a command_type column in each detail table so you can then have an ordinary foreign key constraint between the master and detail tables, and have a check constraint on each detail table to constrain each to contain only a given value of command_type.

 Search for posts by "Celko" on comp.databases or comp.databases.theory, he often posts an example for supertype/subtype representation based on a Vehicle supertype and SUV and Sedan subtypes using this approach. Received on Mon Mar 21 2005 - 09:39:46 CST