Foreign keys slow down Delete

Question

I have a table X that has a column with an auto-incrementing identifier as its primary key. I have other tables A, B, C and D that supplement the information in table X. Each of them should contain a column that refers to the identifier from table X. I did this in my code (Java), and I have a way to return the identifier of each record in table X and use it when pasting into other tables. Everything works well.

Now I am advised to assign these identifier columns in tables A, B, C and D as FOREIGN KEYS, because "this is the right thing." I did it. Now removing rows from table X takes an incredible amount of time to complete . Pasting into other tables takes longer.

Please do not get me wrong, I know why foreign keys are related to the definition of table relationships in the database. But it starts to seem only ceremonial, not actual, especially as my transactions are getting slower.

Questions:

1. Is it worth it to lose some productivity in order to maintain official relations, even if it is not so necessary?

2. Is there a way to speed up transactions and save FOREIGN KEY specifications.

Thanks.

ANSWERS

Here's how the tables were 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, CD are pretty much similar to table A 

Add SQL Foreign Keys:

 ALTER TABLE RelatedTableA ADD ID INT CONSTRAINT fk_refTableX_A FOREIGN KEY (ID) REFERENCES MainTableX(ID) GO -- Same thing with the other related tables 

Decision

I made the Foreign Key columns an index. Now my queries are quick again.

 Create nonclustered index IX_RelatedTableA on RelatedTableA (ID) GO 

Answer 1

If the FOREIGN KEY columns are correctly indexed ( which should happen automatically with the ad, I reckon ), you should see little success in the worst case. Please list CREATE TABLE SQL for each of the tables, because it sounds like there is something wrong.

SQL Server does not automatically create an index on FK columns, so make sure you do it yourself.

The purpose of using a FOREIGN KEY relationship is not to “formally declare” something (or not just do it, anyway). Instead, it provides enough information to ensure the integrity of your data. This means that you cannot mistakenly add your application to add or remove entries to break the relationship. In addition, no other database-based application (such as Management Studio) can do this. It is for this reason - guaranteed enforcement of the rules by the database engine - it is important to declare restrictions.

Answer 2

Foreign keys are not your problem. And you do not want to delete them. When you delete a row from table X, I assume that you first delete rows from tables A, B, C and D? You will need to if you have FK. How do you delete rows from these tables? From your Java application? If so, it would be much faster to configure your FK for cascading deletion. That way, you can make one call to delete a row in table X, and all child rows are automatically deleted by SQL Server. You would save yourself four trips to the database for each deletion from table X.

By the way, there is more to the FK value than just maintaining data integrity (which is huge). If you intend to ever use ORM (like Entity Framework), having FK in place will make your life a lot easier.