• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1593
  • Last Modified:

Foreign key problem in DB2 database

hi
        i have a table called complaints

complaints(uid ,cid,subject description) where uid and cid are composite primary key

and another table called

complaints_access(uid,cid, creationtime)where again  uid and cid are composite primary key

where cid of complaints_access refers to cid of cid of complaints(foreign key).


now it shows the following error

SQL0573N  A column list specified in the references clause of constraint
"CID..." does not identify a unique constraint of the parent table


please help!
0
manikandan689
Asked:
manikandan689
1 Solution
 
ssisworoCommented:
You can not create foreign key only part of the primary key for the referenced table. It has become part of the rules of the relational database. there may be missing, it would be better reviewed its database design.

Here I attach a sample script and desinnya. I hope can help.

--------------------
alter table COMPLAINTS_ACCESS
   drop foreign key "fk_complaints_access";

alter table COMPLAINTS_ACCESS
   add foreign key "fk_complaints_access" (UID, CID)
      references COMPLAINTS (UID, CID)
      on delete restrict;
------------------

 design
0
 
momi_sabagCommented:
foreign keys can only reference primary keys or unique indexes,
so if you want your foreign key to be on cid, you need to create a unique index on cid
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Both of the above comments should help you solve your immediate problem.

But, from a design standpoint, you may want to investigate whether there two table should be rolled up into one table. Obviously, I don't know the full background of what you're trying to accomplish, but it appears that the creationTime attribute more likely belongs in the parent table (complaints).

HTH,
DaveSlash
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now