Q: referential constraints

I have a question about referential integrity constraints on a table.

I want to put a constraint on a table, so that two columns match what is in another table.

The 'lookup' table is table "A". the table that I want to add the constraint to is table "B".

create table A
  ( constraint a_pk primary key (part1, part2),

   part1           varchar2(1),
   part2           varchar2(1),


   ...

   theLastCol      varchar2(20) )

create table B
  ( constraint b_pk primary key (b_key),    b_key number,
...

   part1           varchar2(1),
   part2           varchar2(1),

I want to add a constraint to table B that forces ( b.part1 b.part2 ) to match an occurence of ( a.part1 a.part2 ) in table A.

For example, consider this dataset...

Table A                
part1  part2  ...      
----------------------  
 A     A      ...
 A     B
 C     A

Table B
b_key  part1  part2  ...      
----------------------
1        A      A
2        B      A
3        C      A
4        A      B
5        A      C

The constraint that I want would allow (table B) rows where b_key = 1, 3, and 4; but not allow rows 2 and 5.

How might I get this to work ?
Thanks in advance.

Ray Kruger Received on Fri May 20 1994 - 21:29:05 CEST