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_tab
      constraint 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
------------------------------

Original Message:
Sent: Tue December 16, 2025 06:30 PM
From: Jacob Salomon
Subject: 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_tab
      constraint 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 --------+
------------------------------