SQL Foreign key to a table with a composite primary key

Question

I'm using derby db with NetBeans and I'm having some troubles with my tables:

I've TABLE_A and TABLE_B and i create them like this:

CREATE TABLE TABLE_A ( FIELD_1 varchar(20), FIELD_2 varchar(50), FIELD_3 varchar(2), PRIMARY KEY(FIELD_1,FIELD_2), )

and

CREATE TABLE TABLE_B ( FIELD_1 varchar(20), FIELD_2 varchar(50), FIELD_3 varchar(20), FIELD_4 varchar(25), PRIMARY KEY(FIELD_3), FOREIGN KEY(???) REFERENCES regioni(FIELD_1,FIELD_2) )

The question is: how can I link a TABLE_B record to an another TABLE_A record when TABLE_A has got a 2-fields primary key? What I have to put instead of "???"?

Thank you!!!

If your primary key is made up of two columns, then all your foreign key referencing it must also use all those columns. — marc_s
– marc_s, Commented May 6, 2013 at 15:31
Shouldn't that REFERENCES regioni (...) be REFERENCES TABLE_A (...) ? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 6, 2013 at 18:37

gbn · Accepted Answer · 2013-05-06 15:09:49Z

2

Just use all child columns that match the parent PK

 ... FOREIGN KEY(FIELD_1,FIELD_2) REFERENCES regioni(FIELD_1,FIELD_2) ...

answered May 6, 2013 at 15:09

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:52:53Z

Try this,

CONSTRAINT fk_tbl FOREIGN KEY (FIELD_1,FIELD_2) REFERENCES regioni(FIELD_1,FIELD_2)

Out of the scope of the question but may give some informations why naming constraint is important

What is the purpose of constraint naming

Collectives™ on Stack Overflow

SQL Foreign key to a table with a composite primary key

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related