CODEWITHSUNDEEP
sqldb2
Justin
Justin

Reputation: 1356

Referencing multiple primary keys in another table

I am using DB2 to to create some tables. I have 2 tables that look like this:

create table TEST_TABLE1
/* */
(
PRIMARY_COLUMN1 int not null,
PRIMARY_COLUMN2 varchar(50) not null,
primary key (PRIMARY_COLUMN1,PRIMARY_COLUMN2)
);

create table TEST_TABLE2
/* */
(
PRIMARY_COLUMN1 int references TEST_TABLE1(PRIMARY_COLUMN1) not null,
PRIMARY_COLUMN2 varchar(50) references TEST_TABLE1(PRIMARY_COLUMN2) not null,
primary key(PRIMARY_COLUMN1,PRIMARY_COLUMN2)
);

When I try to add the tables, I get this error:

SQL0573N  A column list specified in the references clause of constraint 
"PRIMARY_COLUMN1..." does not identify a unique constraint of the parent table 
or nickname "DB2INST1.TEST_TABLE1".  SQLSTATE=42890

Does anybody know how I can get TEST_TABLE2 to reference TEST_TABLE1?

I have also tried to create TEST_TABLE2 like this and got the same error:

create table TEST_TABLE2
/* */
(
PRIMARY_COLUMN1 int references TEST_TABLE1(PRIMARY_COLUMN1) not null,

primary key(PRIMARY_COLUMN1)
);

Thanks, Justin

Upvotes: 0

Views: 2876

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

As a thumb rule, foreign key has to refer a unique key only.

The logical explanation would be.. Say you have names 

FIRST LAST
-----------
John Abraham
John Jones

When you create a foreign key refence just to refer FIRST column, there may be two values in the parent. Which is obviously ambiguous! Whereas when you create a reference on including both these columns(which is unique), the ambiguity is no more!

The error message you got is stating the same.

does not identify a unique constraint of the parent table

Now..Analysing ur DDL,

create table TEST_TABLE1
/* */
(
PRIMARY_COLUMN1 int not null,
PRIMARY_COLUMN2 varchar(50) not null,
primary key (PRIMARY_COLUMN1,PRIMARY_COLUMN2)  <<----- Unique over 2 columns
);

You define a PK in Table1 including 2 Columns.

Where-as in second table, you just create a foreign key referring one of those columns! So the error.

create table TEST_TABLE2
/* */
(
PRIMARY_COLUMN1 int 
   references TEST_TABLE1(PRIMARY_COLUMN1) not null, <<--------FK refers only one of them?

primary key(PRIMARY_COLUMN1) 
);

And Solution could be:

create table TEST_TABLE2
/* */
(
PRIMARY_COLUMN1 int  not null,
PRIMARY_COLUMN2 varchar(50) not null,
primary key(PRIMARY_COLUMN1,PRIMARY_COLUMN2),
CONSTRAINT my_foreign FOREIGN KEY (PRIMARY_COLUMN1,PRIMARY_COLUMN2)
                    REFERENCES TEST_TABLE1(PRIMARY_COLUMN1,PRIMARY_COLUMN2)
     <<---- Refer both columns..
);

Upvotes: 2

Related Questions