Should referential integrity be enforced?

database database-design referential-integrity
Muhammad Hasan Khan picture Muhammad Hasan Khan · Aug 23, 2010 · Viewed 14.4k times · Source

One of the reasons why referential integrity should not be enforced is performance. Because Db has to validate all updates against relationships, it just makes things slower but what are the other pros and cons of enforcing and not enforcing?

Because relationships are maintained in the business logic layer anyway, it just makes them redundant for db to do it. What are your thoughts on it?

Answer

paxdiablo picture paxdiablo · Aug 23, 2010

The database is responsible for data. That's it. Period.

If referential integrity is not done in the database, then it's not integrity. It's just trusting people not to do bad things, in which case you probably shouldn't even worry about password-protecting your data either :-)

Who's to say you won't get someone writing their own JDBC-connected client to totally screw up the data, despite your perfectly crafted and bug-free business layer (the fact that it probably won't be bug-free is another issue entirely, mandating that the DB should protect itself).

Related questions

What's wrong with foreign keys?

I remember hearing Joel Spolsky mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database. Do people …

Read More
Can I have multiple primary keys in a single table?

Can I have multiple primary keys in a single table?

Read More
Database, Table and Column Naming Conventions?

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions: Should table names be plural? Should column names be singular? Should …

Read More