Table with no primary key has many referencers via foreign keys

Greetings, Family. I was asked to create a table, say child_tab, with a FK constraint referencing daddy_tab. No problem, says I: alter table child_tab add constraint foreign key(some_code) references daddy_tabconstraint fk_child_daddy; 297: Cannot find unique constraint or primary key on referenced table (daddy_tab).(Table names have been changed to protect me from a chewing out.) Fair enough. Indeed, when I run dbschema on daddy_tab there is indeed no primary key defined. However: Contrary to that error message, the there actually is a unique constraint on the "some_code" column of daddy_tab. Despite the lack of a primary key on daddy_tab, there are 52 other tables referencing this daddy_tab through column(some_code). (Man. that's some big daddy!) I have run dbschema on one of those to confirm what my eyes don't want to believe. How is this possible?! And if it had allowed those foreign keys to reference a merely unique column, why not me? (Geezer discrimination? ;-) I have a guess that once upon a time a unique constraint was enough to serve a primary key but this is no longer allowed under 12.10 or 14.10. Can anyone confirm this? Now, I would like to create a new primary key on daddy_tab but I can't; I get error: 577: A constraint of the same type already exists on the column set.Suppose I were to drop the unique constraint on daddy_tab(some_code) in order get it out of the way in order to create a new primary key. What would happen to all those FK constraints on those 52 tables? Surely they would all vanish! (This happened to me in a rel 7 environment. And yes, I got chewed out.) It would be a devil of a time recreating them. (As I type this, the hamsters in their treadmills in my brain are developing a method. But it's UUUUUGLY!) Too bad I can't insert a "can of worms" emoji here. I have asked a many questions and raised many points. But they are all relevant to the same issue: I still need to create that foreign key. Somehow. Thanks much for help here. I have some possible solutions but they really are ugly and risky. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

Hi Jacob, If you have a unique constraint on a table rather than an explicit primary key, then you can still create a foreign key to it. The catch is that the syntax of the FK must reference the table AND column . In your example, it would be something like, ...references daddy_tab( some_code ) constraint fk_child_daddy; If you were to drop the unique constraint, then yes, the foreign keys would all disappear...so be careful if you do go down that route! Mike ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Mike! Thank so much! That works. Sadly, I had indeed kiboshed all those FKs referencing my daddy_tab. Fortunately I was able to get the list from another server, past that into a file, and use a Perl script to regenerate the alter statements. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------