3 ms·

I don't use foreign keys because they've bitten me in the ass too many times. I can maintain data integrity in the app level so I do it there. Adding FKs to the

I don't use foreign keys because they've bitten me in the ass too many times. I can maintain data integrity in the app level so I do it there. Adding FKs to the mix means my DB is also doing the work I already have to deal with in the app and that's use slowing things down. FKs are a kind of business logic and I want none of that in my DB. I want my DB to give me storage, preferable ACID storage, and nothing more. I'll keep my business logic in my app.

skylan_q 13y ago
All they've done for me (in my experience) is slowed down development and saved me from nothing. The only downside I see to not using them is not being able to use FK's in indexing.
- chris_wot 13y ago
  Do you maintain your apps? Do they ever need to scale? Ever need to use an app or db cluster? If so, then you'll realise why they are necessary.
clavalle 13y ago
>I want my DB to give me storage, preferable ACID storage, and nothing more. Yikes! You are missing the 'C' in ACID at the very least. If what you say is true, a relational database might not be what you need to be using. I think it is more likely that a relational database would be helpful to you but you worry about data after you worry about logic. A dangerous approach in many cases IMHO.
sehrope 13y ago
I don't see it this way. Foreign keys in your database allow you do define relationships that must be maintained across your data set. Without them it's possible for a bug in your application code to create a logical state that makes no sense (ex: child records with no parent). Just like data type checks (ex: regex whitelisting for field values) they need to be done at both the front end and backed and yes some times it's a pain but in the long run that's a positive not a negative. If you expose your database to a whole new app, say v2 of your app or an orthogonal admin interface, you can be assured of relational guarantees between your tables.
losvedir 13y ago
Wow, I can't fathom how this works. Where do you store the relationship, then? Say you have posts and comments. I would want a posts table and a comments table, with a post_id foreign key in comments. How would you do it without a foreign key?
- chris_wot 13y ago
  He still has the key field, but doesn't add the constraint.
chris_wot 13y ago
This is a pretty uninformed post. You want a consistent database, then add foreign keys. You'll need to ensure that you get the application layer to enforce it also, but you also get the advantage of a constraint at the database so it won't ever become inconsistent. Keeping your "business logic" in the app as reasoning... You could just say that the business logic is built into the schema and shouldn't be there and just use a key store (many people do this, actually...); or you do all your transactions in the app. To my way of thinking, that's short-sighted. As for slowing you down, FKs shouldn't, in terms of development (a good ORM handles it with ease), or in terms of physical performance (most databases handle performance constraints at this level via query optimisation and join strategies, at its worst you do bulk updates via a database facility).

skylan_q 13y ago

All they've done for me (in my experience) is slowed down development and saved me from nothing. The only downside I see to not using them is not being able to use FK's in indexing.

chris_wot 13y ago
Do you maintain your apps? Do they ever need to scale? Ever need to use an app or db cluster? If so, then you'll realise why they are necessary.

clavalle 13y ago

>I want my DB to give me storage, preferable ACID storage, and nothing more. Yikes! You are missing the 'C' in ACID at the very least. If what you say is true, a relational database might not be what you need to be using. I think it is more likely that a relational database would be helpful to you but you worry about data after you worry about logic. A dangerous approach in many cases IMHO.

sehrope 13y ago

I don't see it this way. Foreign keys in your database allow you do define relationships that must be maintained across your data set. Without them it's possible for a bug in your application code to create a logical state that makes no sense (ex: child records with no parent). Just like data type checks (ex: regex whitelisting for field values) they need to be done at both the front end and backed and yes some times it's a pain but in the long run that's a positive not a negative. If you expose your database to a whole new app, say v2 of your app or an orthogonal admin interface, you can be assured of relational guarantees between your tables.

losvedir 13y ago

Wow, I can't fathom how this works. Where do you store the relationship, then? Say you have posts and comments. I would want a posts table and a comments table, with a post_id foreign key in comments. How would you do it without a foreign key?

chris_wot 13y ago
He still has the key field, but doesn't add the constraint.

chris_wot 13y ago

This is a pretty uninformed post. You want a consistent database, then add foreign keys. You'll need to ensure that you get the application layer to enforce it also, but you also get the advantage of a constraint at the database so it won't ever become inconsistent. Keeping your "business logic" in the app as reasoning... You could just say that the business logic is built into the schema and shouldn't be there and just use a key store (many people do this, actually...); or you do all your transactions in the app. To my way of thinking, that's short-sighted. As for slowing you down, FKs shouldn't, in terms of development (a good ORM handles it with ease), or in terms of physical performance (most databases handle performance constraints at this level via query optimisation and join strategies, at its worst you do bulk updates via a database facility).