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, May 06 '13 at 15:31
Shouldn't that `REFERENCES regioni (...)` be `REFERENCES TABLE_A (...)` ? — ypercubeᵀᴹ, May 06 '13 at 18:37

score 2 · Accepted Answer · answered May 06 '13 at 15:09

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 06 '13 at 15:09

gbn

422,506
82
585
676

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

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

edited May 23 '17 at 11:52

Community

1
1

answered May 06 '13 at 15:09

John Woo

258,903
69
498
492

SQL Foreign key to a table with a composite primary key

2 Answers2