add check constraint

[huchen]

Here is my problem:
I have table A(code_type, code, description),
(A.code_type, A.code) is unique.

Another table B(col1, col2, col3)
I want to add a constraint on table B to check B.col2 exist in A.code

When I run this:
alter table B add constraint fk_ab foreign key (col2)
references A(code);
I got this error:
no matching unique or primary key for this column-list

How can I enforce a check on B.col2 to make sure the b.col2 exist in A.code?

Thanks in advance.

 

[SantaMufasa]

(A.code_type, A.code) is unique.

Since A.code, by itself, is not unique, it cannot be the target of a foreign key. Therefore, you can build an INSERT/UPDATE trigger on the B table to check and see that B.col2 matches an A.code before it successfully INSERTs/UPDATEs into B.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[huchen]

I think the trigger is good solution. Thank you Santa.