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!
manikandan689Asked:
Who is Participating?
 
ssisworoConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.