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

Ron <ronljohnsonjr@xxxxxxxxx> · Mon, 30 Jan 2023 15:55:38 -0600



    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.