Re: Creating foreign key

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 1 May 2021 01:06:15 -0400
Message-ID: <ab77b596-4f10-0516-5405-24f733089656_at_gmail.com>

Replies in-line:

Hello All, We are using version 11.2.0.4 of Oracle.

That's not good. That version is old and unsupported.

We have a requirement in which we want to create foreign key on a new table which is going to reference one of the big transaction table holding ~1 billion+ rows. The transaction table holds a unique index on that column but doesn't have an unique constraint , so the foreign key creation on the new table is failing(  ORA-02270: no matching unique or primary key   ) . 

That is a bad design. Foreign keys should be thought as a list of values. Something like checking that ZIP code actually exists. You would have a relatively small reference table and a big transaction table would have a foreign key pointing to the reference table. Pointing foreign key into the huge transaction table makes no sense whatsoever. Please, tell that to whoever is suggesting such design.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

-- http://www.freelists.org/webpage/oracle-l Received on Sat May 01 2021 - 07:06:15 CEST