Re: Reference table and primary key

Phil Goden wrote:
>
> Can anyone help
>
> I am attempting to create the three tables listed below, however, the
> lu_group_process fails because rangeNumber is not a primary key nor has it a
> unique index. How do I get around this without building a compound key in
> lu_group and without having to include a compound key from lu_group on
> lu_group_process table.
>
> create table lu_group(
> id number(8) not null
> ,startRange varchar2(5) not null
> ,endRange varchar2(5) not null
> ,rangeNumber number(4) not null
> ,constraint group_pk primary key (id) using index tablespace
> &&my_index_tablespace
> );
>
> create table lu_Process(
> id number(8) not null
> ,description varchar2(30) not null
> ,constraint process_pk primary key (id) using index tablespace
> &&my_index_tablespace
> );
>
> create table lu_group_process (
> id number(8) not null
> ,rangeNumber number(4) not null
> ,processID number(8) not null
> ,constraint groupProcess_group_fk foreign key (rangeNumber) references
> lu_group(rangeNumber)
> ,constraint groupProcess_process_fk foreign key (processID) references
> lu_process(id)
> );
>
> in the tables, I am hoping to have entries such as
>
> lu_group:

 id start end range
> 1 | 'A' | 'C' | 1
> 2 | 'D' | 'E' | 1
> 3 | 'F' | 'p' | 1
> 4 | 'Q' | 'Z' | 1
> 5 | 'A' | 'Z' | 2
>
> lu_process:

  id processid
> 1 | 'Process1'
> 2 | 'Process2'
> 3 | 'Process3'
>
> lu_group_process:

 id range processid
> 1 | 1 | 1
> 2 | 1 | 2
> 3 | 2 | 2

I keep looking at your example and I don't understand what you are trying to accomplish. It looks like ID is doing two different things and RangeNumber doesn't mean anything without the lu_group.ID

Now I get it, you started to denormalize your tables. Maybe you should revisit your design. I don't think you what to do what you appear to be trying with these tables. the primary key of the first table, lu_group, really is the combination of ID and RangeNumber.

try this:
> lu_group: (no rangeNumber)

 gid start end
> 1 | 'A' | 'C'
> 2 | 'D' | 'E'
> 3 | 'F' | 'p'
> 4 | 'Q' | 'Z'
> 5 | 'A' | 'Z'
>
> lu_process:

  pid processid
> 1 | 'Process1'
> 2 | 'Process2'
> 3 | 'Process3'
>
> lu_group_process:

 gid pid
> 1 | 1
> 2 | 2
> 3 | 2

Is that closer to what you need?

HTH
Ed Prochak Received on Fri Oct 05 2001 - 21:47:09 CDT