99fa0cae   
   From: esquel@sommarskog.se   
      
   Dave (djohannsen2@gmail.com) writes:   
   > At work I uncovered a join that (foreign key) references only part of   
   > composite primary key on another table.  the data in the composite PK   
   > table have over 200 M rows - so the ensuing table scanning is killing   
   > the performance.  i'd like to know some options for best addressing   
   > how to fix this.   
   >   
   > E.g. table ABC (composite PK: fieldID1, fieldID2).   
   > table DEF (PK: fieldID3) - references only fieldID1.   
   > table MNO (PK: fieldID4) - references only fieldID2.   
   > i.e. table DEF only has access to fieldID1, and MNO only has access to   
   > fieldID2.   
   >   
   > some options that i'm not very fond of are:   
   > - adding fieldID2 to table DEF, and fieldID1 to table MNO.   
   > - OR creating a relation tables between ABC, and DEF with fields:   
   > fieldID3, fieldID1, fieldID2, also with composite PK for RI (and   
   > similar relation table for ABC to MNO)   
   > - replacing the composite key PK with single valued identity value   
   > does not seem like a good plan either, as the data lends itself very   
   > well to having a composite key PK.   
      
   You will need to explain this a little more. First, you cannot add a   
   foreign key that references only part of a key:   
      
      CREATE TABLE mother (a int NOT NULL,   
                           b int NOT NULL,   
                           data char(12) NULL,   
                           CONSTRAINT pk_mother PRIMARY KEY (a, b))   
      go   
      CREATE TABLE stepchild (a int NOT NULL)   
      go   
      ALTER TABLE stepchild ADD CONSTRAINT fk   
         FOREIGN KEY (a) REFERENCES mother(b)   
      go   
      DROP TABLE mother, stepchild   
      
   yields:   
      
      Msg 1776, Level 16, State 0, Line 1   
      There are no primary or candidate keys in the referenced table 'mother'   
      that match the referencing column list in the foreign key 'fk'.   
      Msg 1750, Level 16, State 0, Line 1   
      Could not create constraint. See previous errors.   
      
   Are these foreign keys enforced through triggers?   
      
   Neither is it clear from the post where this leads to performance problems.   
   I can make some guesses, but I'd prefer more information than shooting   
   in the dark.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)