sql - Foreign Keys Slowing down Delete -

i have table x has auto-incremented id column primary key. have other tables a, b, c , d compliment info in table x. each of these have contain column references id table x. have done , in code (java) , have way of returning id of each entry table x , using when inserting other tables. working well.

now, i've been advised assign id columns on tables a, b, c , d foreign keys because "it's right thing do". did that. now deleting rows table x takes incredible amount of time complete. insertion other tables takes longer too.

please don't me wrong, know why foreign keys relevant specify relationships of tables on db. it's starting seem ceremonial rather relevant transactions becoming slower.

questions:

1. worth lose performance in bid keep relationships officially specified though it's not necessary?

2. there way can speed transactions , still keep foreign key specifications.

thanks.

replies

here how tables created.

create tables sql:

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 pretty similar table 

add foreign keys sql:

alter table relatedtablea add id int  constraint fk_reftablex_a foreign key (id) references maintablex(id)  go -- same thing other related tables 

solution

i made foreign key columns index. queries fast again.

create nonclustered index ix_relatedtablea  on relatedtablea (id)  go 

if foreign key columns correctly indexed (which should happen automatically declaration, believe) should see @ worst small performance hit. please list create table sql each of tables because sounds wrong there.

sql server not automatically create index on fk column(s), make sure yourself.

the purpose of using foreign key relationships not "officially declare" (or not just that, in case). instead, gives database engine sufficient information enforce integrity of data. means can't erroneously have application add or delete records such relationship violated. in addition, no other application using database (such management studio) can so, either. it's reason -- guaranteed enforcement of rules database engine -- declaring constraints important.