title: Foreign Keys Slowing down Delete
body: <p>I have a table X which has an auto-incremented ID column as its primary key. I have other tables A, B, C and D that compliment the info in table X. Each of these have to contain a column that references the ID from table X. I have done that and in my code (Java) and I have a way of returning the ID of each entry to table X and using that when inserting to the other tables. All is working well. </p>
  
  <p>Now, I've been advised to assign those ID columns on tables A, B, C and D as FOREIGN KEYS because "it's the right thing to do". I did that. <strong>Now deleting rows from table X takes an incredible amount of time to complete</strong>. Insertion to the other tables takes longer too. </p>
  
  <p>Please don't get me wrong, I know why Foreign Keys are relevant to specify relationships of tables on a DB. But it's starting to seem only ceremonial rather than actually relevant especially as my transactions are becoming slower. </p>
  
  <p><em>Questions:</em></p>
  
  <p><em>1. Is it worth it to lose some performance in a bid to keep relationships officially specified even though it's not that necessary?</em> </p>
  
  <p><em>2. Is there any way I can speed up my transactions and still keep the FOREIGN KEY specifications.</em></p>
  
  <p>Thanks.</p>
  
  <p><strong>REPLIES</strong></p>
  
  <p>Here is how the tables were created. </p>
  
  <p>CREATE Tables SQL:</p>
  
  <pre><code>CREATE TABLE [dbo].[MainTableX](
      [col1] [smalldatetime] ,
      [col2] [varchar] (20) ,
      [ID] [int] IDENTITY(1,1) NOT NULL,
   CONSTRAINT [PK_MainTableX] PRIMARY KEY CLUSTERED 
  (
      [ID] ASC
  )
  )
  GO
  
  
  CREATE TABLE [dbo].[RelatedTableA](
      [differentID] [varchar] (50),
      [detail1] [varchar] (40),
      [detail2] [varchar] (40),
   CONSTRAINT [PK_RelatedTableA] PRIMARY KEY CLUSTERED 
  (
      [differentID] ASC
  )
  GO
  
  -- Tables B, C D are pretty much similar to table A
  </code></pre>
  
  <p>Add Foreign Keys SQL:</p>
  
  <pre><code>ALTER TABLE RelatedTableA ADD ID INT 
  CONSTRAINT fk_refTableX_A FOREIGN KEY (ID)
  REFERENCES MainTableX(ID) 
  GO
  -- Same thing with the other related tables
  </code></pre>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>I made the Foreign Key columns an index. Now my queries are fast again.</p>
  
  <pre><code>Create nonclustered index IX_RelatedTableA 
  on RelatedTableA (ID) 
  GO
  </code></pre>