Re: Creating foreign key

Try this:

create table t1 pctfree 90 pctused 10 as select * from all_objects;

alter table t1 modify object_id null;

create unique index t1_i1 on t1(object_id) pctfree 90;

alter system flush buffer_cache;

alter session set events '10046 trace name context forever, level 8';

alter table t1 add constraint t1_uk unique(object_id);

alter session set events '10046 trace name context off';

Tested on 11.2.0.4
You should find that you have a unique constraint on the table protected by the index, with virtually no work done (apart from the dictionary checks and updates).

If you want a little more "physical" confidence, create a table as "select 10M rows from your big table" and do the same "unique index", flush, add constraint cycle and see how little work it takes.

Regards
Jonathan Lewis

On Fri, 30 Apr 2021 at 20:06, Lok P <loknath.73_at_gmail.com> wrote:

> Hello All, We are using version 11.2.0.4 of Oracle. 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 * ) .
>
> So my doubt was, to create the foreign key in the fastest way we will
> first need to add the unique constraint to the big transaction table and
> for that , if the "älter table add constraint con1 unique ( col1)... using
> index Idx1 "will be the fastest approach here?
>
> Regards
> Lok
>

--
http://www.freelists.org/webpage/oracle-l