Toad World® Forums

Constraint problem

HI.

I’m doing a migration from mysql to db2, but i can’t do a constraint in the table EVENTS:

CONSTRAINT fk_EVENTS_CLIENT_SCHEDULES1 FOREIGN KEY (schedule_name, chg_time) REFERENCES client_schedules (schedule_name, chg_time) ON DELETE NO ACTION ON UPDATE NO ACTION,

db2 "ALTER TABLE  events
      ADD CONSTRAINT fk_EVENTS_CLIENT_SCHEDULES1 FOREIGN KEY (schedule_name, chg_time) REFERENCES client_schedules (schedule_name, chg_time) ON DELETE NO ACTION ON UPDATE NO ACTION"

the problem is that the table client_schedules have the next primary key:

PRIMARY KEY (schedule_name,chg_time,node_name),

I can put node_name in the constraint but the problem is that I only want this relacion :

fk_EVENTS_CLIENT_SCHEDULES1 FOREIGN KEY (schedule_name, chg_time) REFERENCES client_schedules (schedule_name, chg_time)

with only this two colummns like in Mysql

here the output:

[db2inst1@ITSTSMR01 ~]$ db2 "ALTER TABLE  events
>       ADD CONSTRAINT fk_EVENTS_CLIENT_SCHEDULES1 FOREIGN KEY (schedule_name, chg_time) REFERENCES client_schedules (schedule_name, chg_time) ON DELETE NO ACTION ON UPDATE NO ACTION"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0573N  A column list specified in the references clause of constraint
"FK_EVENTS_CLIENT_SCHEDULES1" does not identify a unique constraint of the
parent table or nickname "DB2INST1.CLIENT_SCHEDULES".  SQLSTATE=42890

Sorry for my bad english

Hi - Please create a unique constraint on the parent table’s column something like this,

ALTER TABLE . ADD CONSTRAINT Schem_Table1_UK UNIQUE(Col);

and then try that command again. it should work.

Regards

Glenn