constraint on composite foreign keys

[peterzh]

Hi Guys,
Here is my question. I have two tables A and B:

Table A (mht_nbr, act_seq_nbr) and
Table B (mht_nbr, act_seq_nbr, src_cde)

In table B all three fields are primary keys.

I want to have a constraint on field act_seq_nbr in Table A:
any value of this field must come from the values of the field act_seq_nbr in Table B.

Thanks in advance for your help!

Peter

 

[jbenson001]

Create a foreign key from table B to Table A on act_seq_nbr.

Jim

 

[peterzh]

Sorry for the mistake in my question. It should be the other way round.

Any value of the field act_seq_nbr in Table B should come from the value of the field act_seq_nbr in Table A.

In Table A each mht_itl_nbr can have multiple act_seq_nbr.
In Table B each mht_itl_nbr has only one act_seq_nbr, and this value must be from Table A.

Because there are three key fields in Table B so I could not use act_seq_nbr in Table B referencing the field in Table A.

Thanks for your quick reply, Jim.

Peter

 

[r937]

i assume the primary key of Table A is (mht_nbr, act_seq_nbr)

so just declare the foreign key in Table B as (mht_nbr, act_seq_nbr) referencing table A

r937.com | rudy.ca