I know that foreign key guarantees data integrity, and prevent orphaned rows.
But, I also know that foreign key will make performance slower in INSERT, DELETE, and UPDATE. And I heard many people do not use foreign key due to the performance.
And, I think orphaned rows is better than slow performance since I can clean it myself on maintenance.
So, which one is better ? Foreign key or not for MySQL ?
Should we just use foreign key for simple database system (such as personal blog) ? Or, should we use foreign key for complex database system ?
[quote=“terrydoang, post:1, topic:191464, full:true”]
And, I think orphaned rows is better than slow performance since I can clean it myself on maintenance.[/quote]seems like you have already made up your mind, so go ahead and sacrifice accuracy for speed
on the off chance that you detected some sarcasm in that previous comment, it was intended
Don’t try to solve problems that you don’t have. Especially, not by ignoring basic principles of relational database architecture. Stop thinking so much and follow tried and true methods. Honestly, if you told me that is the reason you decided to not use foreign keys I would just look at you and laugh as there are so many other ways to improve performance that are much less hazardous to the state of the persistence layer. Also if you are dealing with that problem in the first place than I would question as to using a relational database is the best approach in the first place.
Which one do you prefer : using CASCADE or RESTRICT ?
They say using CASCADE will break the business logic because database responsibility is just for storing data, not doing business logic.
But, of course, with CASCADE, everything’s gonna be easy. (Less code)
In that person’s context, a delete should NOT have been used. A “logical” delete where the record is flagged and the flag determines whether the record should be displayed or not.
It’s no different when performing any kind of database operation. You need to be careful before performing any operation, and the appropriate checks/balances need to be in placed.
[quote=“terrydoang, post:4, topic:191464, full:true”]
Which one do you prefer : using CASCADE or RESTRICT ?[/quote]this is a trivially silly question, because they do different things, and you would not choose the wrong one in a given circumstance, you would choose the one that fits the business requirements
[quote=“terrydoang, post:4, topic:191464, full:true”]They say using CASCADE will break the business logic because database responsibility is just for storing data, not doing business logic.[/quote]to put it politely, that’s garbage
stop listening to those sources of advice
database responsibility is for storing data accurately
relationships between tables are part of accuracy
but, of course, with a hammer, everything’s gonna be easy (less choosing between slotted or phillips or robertson)
@terrydoang When I first starting working with databases, all my queries were like SELECT * FROM and my tables were “everything in one big table”
Then I used PHP to do the heavy lifting.
I was more familiar with PHP than I was with SQL and the code “worked” - BUT - as my skill level improved and things got more complex and larger I soon learned the error of my ways.
I’m still no where near being a database expert, but trust me, don’t be hesitant to pass some of the work off to the database when the capabilities are there to use.
