Re: Foreign keys

Rob Sargent <robjsargent@xxxxxxxxx> · Wed, 18 Dec 2013 11:10:03 -0700



    On 12/18/2013 11:02 AM, Dean Gibson (DB
      Administrator) wrote:

    
      I have general question about FOREIGN KEYs:

      
        Suppose I have table A with primary key X, and another table
          B with field Y.
        When I 'ALTER TABLE "B"  ADD FOREIGN KEY( "Y" )  REFERENCES
          "A"  ON UPDATE CASCADE  ON DELETE CASCADE', that clearly
          spends some time building a separate index.  Since there is
          already a unique index on X, presumably (?) the index being
          built is on Y.
        However, the PostgreSQL documentation seems to indicate that
          it's a good idea to also separately create an index on Y.
        Why, and why is the FOREIGN KEY index different from the
          ones on X and Y in any way but trivial?
        If I need the separate index on Y, should it be built before
          or after the FOREIGN KEY constraint?

        
      -- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
    
    Perhaps
        you want http://www.postgresql.org/docs/9.3/static/sql-altertable.html

    add table_constraint_using_index