Re: Foreign key creation on table with huge record count.

On 1/30/23 14:12, Gambhir Singh wrote:
> Hi,
>
> I have a table with a record count of around 100 Million records. while
> creating foreign key on the table took a significant amount time to get
> created.
>
> Is there any way to speed up the execution of creation of foreign key
> constraint ?

I combine the suggestions of the other two emails:
1. Make sure that there's an index on the relevant field of the referenced
table.
2. ALTER TABLE foo ADD CONSTRAINT foo_fk FOREIGN KEY(bar) REFERENCES blarge
(bar) NOT VALID;
3. ALTER TABLE foo VALIDATE CONSTRAINT.

It's *really* fast.

(Step 1 is vital for operations like purging old records.  Without that
index deletes can take months.)

--
Born in Arizona, moved to Babylonia.