How important is referential integrity? Don’t act so surprised that I even asked the question. I remember a time when SQL Server didn’t support declarative referential integrity. You had to build your own referential integrity by using triggers.
Just a note: you can build referential integrity across databases with triggers. I don’t recommend it because of the overhead; but, it is possible. Using a synonym can make the virtual relationship even easier to create and maintain.
Back to the topic. The point is that you don’t have to build in referential integrity into your database, and that by not creating it your database will perform faster and be easier to maintain In some ways because it does not enforce data integrity.
For example, consider those times when you want to completely empty a table, regardless of its contents. TRUNCATE TABLE [Table Name] will delete all the rows in a table, unless the table is used as a foreign key for another table. In that case, SQL Server won’t even allow the command to be executed, even if the table maintaining the relationship is empty.
So, I know I am drumming up the troops here and that referential integrity is there to help maintain pure data. Personally I always enable it. But, are there times when you should be allowed to disable referential integrity? Are the benefits more important than the potential data corruption? I’m just asking?
Share your position on referential integrity. You can leave comments here, or send your thoughts in an Email to btaylor@sswug.org.
Cheers,
Ben