creating many-many relationships in javadb

[odeno]

Hi,

I need to create some many-many relationships between two tables and want to use a link table to do this.

It is rather simple and standard but can't figure out how to do this in javadb.

tab1
pk1
..
..

tab2
pk2
....

tab1_tab2
pf_k1
pf_k2
/* the above represent a compound primary key */

How on earth do I create the above in javadb / derbydb?

I've tried the following:-

Alter table tab1 add constraint tab1_fk foreign key (pk1) references tab1_tab2 (pf_k1)

and likewise for tab2 and pk2 but I get the following error:-

"Error code -1, SQL state X0Y44: Constraint 'TAB1_FK' is invalid: there is no unique or primary key constraint on table '"APP"."DEL_SEM"' that matches the number and types of the columns in the foreign key."

I've tried so many combinations and searched and searched the web to no avail. Hoping someone here can help.....

thanks,

[odeno]

sorry the error is:-


"Error code -1, SQL state X0Y44: Constraint 'TAB1_FK' is invalid: there is no unique or primary key constraint on table "tab1_tab2"' that matches the number and types of the columns in the foreign key."

[masijade]

pf_k1 needes to be defined as a prmary key or as a unique constraint (which is exactly what that exception says) before you can reference it as a foreign key.

[odeno]

thanks - yes had realised this and solved it.

thanks againfor replying....