Foreign key contraints: good or bad?

[allspiritseve]

I noticed this article today: http://www.sitepoint.com/blogs/2009/...e-development/

I've never used explicit foreign key contraints before. Looking at this section:

ALTER TABLE borrowed 
ADD CONSTRAINT FK_borrowed 
FOREIGN KEY (employeeid) REFERENCES employee(id) 
ON UPDATE CASCADE
ON DELETE CASCADE; 

It seems to me like this is using the database for business logic. If that's true, then which is the better location for keeping referential integrity between a primary key on one table and a foreign key on another? PHP or MySQL? Also, could DB views be considered business logic? How do you decide how much to have the database do, and how much to do yourself?

[Czaries]

MySQL is a Relational Database. It's OK for the database to know about table relations. I started setting up all my tables like this with foreign key constraints about a year ago, and have never looked back. Anything that makes my life easier and eliminates orphaned records with no upkeep is a win in my book, whether or not it's the "right" way to do it.

[Vali]

You can't do that in all MySQL databases, so you should have that in your code.
If the database you plan to use can use foreign keys, and you plan never to change that database to something else, then you can put that logic in the database.

[matthijsA]

I use them as well. That way keeping the data integrity is not dependent on my code and a lot easier to achieve. If I make an error in my code and try to do something to the data which isn't allowed I get a nice error/warning from the database.

In the same way that you set the data field type to a certain type (say VARCHAR or INT), foreign key restrictions are a way to keep the data model rules close to where they belong, in the database.

[felgall]

It doesn't hurt to define the foreign keys (even if mySQL using ISAM doesn't support it). It provides an additional level of validation where it is supported and if you know your database supports it then it makes some database calls much simpler to implement.

[r937]

It seems to me like this is using the database for business logic. If that's true, then which is the better location for keeping referential integrity between a primary key on one table and a foreign key on another? PHP or MySQL?

anything to do with the correctness of the data should be handled by the database engine, if at all possible

there are instances where you cannot implement certain rules in the database, and then it would be okay to use application logic

but if the database can enforce something, you really should let the database enforce it

and a business rule like "don't have any books loaned out to employees that don't exist" is a great example of a business rule that could and should be enforced by the database

you declare it in a few lines of SQL, and you're done

do you really want to bloat the app with the same logic? in all the places where the app touches those tables?

Also, could DB views be considered business logic? How do you decide how much to have the database do, and how much to do yourself?

views are something else, but yeah, i guess they could be interpreted as business rules -- for example, don't show ~everybody~ the contents of the employee salary column, just authorized users in HR

again, do you really want to bloat the app with the same logic? in all the places where the app touches those tables?

so the correct answer is: get the database to do as much as possible

[Mastodont]

which is the better location for keeping referential integrity between a primary key on one table and a foreign key on another? PHP or MySQL?
Integrity between database tables belongs naturally to database, not outside to application. So correct answer is PostgreSQL, Firebird, SQL Server or any other database.

could DB views be considered business logic
Yes.

How do you decide how much to have the database do, and how much to do yourself?
It depends. For example, constraints are to be checked on application level to eliminate unavailing round-trips with wrong data for INSERT or UPDATE. But triggers are fine and also stored procedures for some business logic.

[allspiritseve]

Cool, sounds like a pretty definitive answer from everyone.

do you really want to bloat the app with the same logic? in all the places where the app touches those tables?

Doesn't the app need to know these rules anyways?

[r937]

For example, constraints are to be checked on application level to eliminate unavailing round-trips with wrong data for INSERT or UPDATE.

presumably you are talking about inserting a row that already exists, or updating a row that doesn't exist

how do you check a constraint at the application level without actually doing a SELECT first? thus, your scenario is:

• run SELECT
• if no error, run INSERT or UPDATE
• else, display error message

however, this is twice as slow as it needs to be -- do this instead:

• run INSERT or UPDATE
• if no error, you're done
• else, display error message

see? twice as efficient if you let the database handle it

Doesn't the app need to know these rules anyways?

in general, nope

[allspiritseve]

in general, nope

Why not? Isn't it more important to check that a query satisfies specified requirements before submitting than to assume the database will check it for you (at least from the end-user's perspective)? That way, you can give the user more fine-grained feedback than just "insert failed", because you know exactly why the query failed... no borrower was selected when you tried to borrow a book.

[Mastodont]

presumably you are talking about inserting a row that already exists, or updating a row that doesn't exist

No, I am talking about constraints on field or row level:
- column "gender" accepts only strings "Mr.", "Mrs." and "Miss"
- event start date must not be greater than end date

etc.

[r937]

there are ways to declare those constraints in the database

yes, you might want to check those things in the application...

... but you must also check them in the database!!!

the application is seldom the only way that data gets into the database

[ctx2002]

"It seems to me like this is using the database for business logic"
foreign key is an relation that is translated from business logic. if there is no
business logic, no relation needed.

also foreign key keeping data integrity among tables. so for data integrity reason , foreign key relation is belongs to DB, not business App.

"I've never used explicit foreign key constraints before."
no need to use foreign key , if you want to develop you own code to maintain data consistency.

"Doesn't the app need to know these rules anyways?" the App needs to know business rules , but why not let DB check data integrity for you ?

[allspiritseve]

there are ways to declare those constraints in the database

yes, you might want to check those things in the application...

... but you must also check them in the database!!!

the application is seldom the only way that data gets into the database

In my opinion, the database's contraints should be less than or equal to the contraints in the app. If your database is more selective than your app, then you get failed queries and the user doesn't know why, because the app let that data through. The database should be the last line of defense. I'm not arguing against keeping the constraints in the db, just saying that they're duplicating constraints in the app that I feel are more important because they allow the user to retry submitting. A failed query doesn't do that.

[r937]

... they're duplicating constraints in the app that I feel are more important because they allow the user to retry submitting. A failed query doesn't do that.

that largely depends on the skillz of the query programmer

surely you don't just print "query error" but actually translate the error code that the database issues...

[allspiritseve]

that largely depends on the skillz of the query programmer

surely you don't just print "query error" but actually translate the error code that the database issues...

Actually I check in my app and make sure everything is valid before the query's even submitted. If the data isn't valid, I give the user helpful feedback. If the data is valid and the query fails, it's due to a bug, not bad data, so I tell the user such and ship off an email telling me to FIX IT!

[SirAdrian]

Actually I check in my app and make sure everything is valid before the query's even submitted. If the data isn't valid, I give the user helpful feedback. If the data is valid and the query fails, it's due to a bug, not bad data, so I tell the user such and ship off an email telling me to FIX IT!

I think the point is that you can take some of the validation out and let the DB do it's job. Most people see a DB error as a bug, but it's not always the case.

For example, when inserting possibly duplicate key rows, check for a duplicate row error, and you could throw it as an exception and let the application then handle how it affects the user -- it doesn't have to translate to a db error page. It's more efficient than running an extra select first and then inserting.

[felgall]

Everything you can offload from the application into the database will make the processing more efficient. Also the more info you give the database about the data relationships the more efficiently it can handle the requests you do give to it.

[Kailash Badu]

Everything you can offload from the application into the database will make the processing more efficient. Also the more info you give the database about the data relationships the more efficiently it can handle the requests you do give to it.

I would take that statement with a grain of salt. Offloading everything possible from application to database is hardly an ideal design choice. This tends to make the database slower and consequently the application less efficient. That is why I usually limit the amount of processing ported to database to those that are strictly about data, tables, and relationships. Stored procedure, for example, allow you to move a big chunk of processing from application to the database. However, if you abuse the feature and move as much logic to database as possible you end up with a terribly inefficient system that is not only slow but also nightmarish to maintain.

As for why Foreign Keys: It’s important to remember that a database can have multiple access points. Several disparate applications could be running off the same database and in rare occasions a DBA could directly operate on the database. Having data integrity constrains like foreign key directly in database come immensely handy in these situations.

[allspiritseve]

It’s important to remember that a database can have multiple access points. Several disparate applications could be running off the same database and in rare occasions a DBA could directly operate on the database. Having data integrity constrains like foreign key directly in database come immensely handy in these situations.

That I can completely understand. I don't think foreign key constraints are a replacement for proper input validation, though.

[felgall]

I would take that statement with a grain of salt.

What you are doing by providing all that info to the database is to give it more information about the data the database contains so that it can be MORE efficient at processing the data in the database. Not giving the database that information means that the database has less information about the data and therefore may not chose the most effective way of processing it. Supplying the database with more info about the data can only make processing faster - not slower. At worst it will make no difference.

[Cups]

"Jim Jackson in accounts, he's left now hasn't he?"

"So?"

"Did he bring all those books back?"

" ... which books?"

"Computer says no ..."

Just who decides on, sets and adjusts the business rules may influence whether you want delegate this power to the rdbms from the get go.

I suppose as a programmer controlling the actions of a dba (or you are one and the same) does the choice of whether to hard wire these decisions into the db balance on pretty variable management whims?

I think you'd want to be very sure before doing it, thats all I mean.

[ahundiak]

Actually I check in my app and make sure everything is valid before the query's even submitted. If the data isn't valid, I give the user helpful feedback. If the data is valid and the query fails, it's due to a bug, not bad data, so I tell the user such and ship off an email telling me to FIX IT!

Not necessarily unless you lock tables as part of your validation process. While you are doing the checks another process could slip in and modify the database thus invalidating your original checks.

[allspiritseve]

Not necessarily unless you lock tables as part of your validation process. While you are doing the checks another process could slip in and modify the database thus invalidating your original checks.

My validation doesn't hinge on checking the database to determine if the data is valid. That obviously would become more important the more people you have editing the same data, but the majority of the time validation should be able to catch errors before any queries are made. If it's possible for me to check it before I make the query, I would prefer that 100% of the time over letting the database do the validation because then I can give fine-grained feedback back to the user. That isn't to say having the database do a second check isn't valid... it's just unnecessary in a lot of cases.

[XtrEM3]

My validation doesn't hinge on checking the database to determine if the data is valid. That obviously would become more important the more people you have editing the same data, but the majority of the time validation should be able to catch errors before any queries are made.

Surely you have some integrity constraints? I find it common to check whether a user id is valid before inserting a comment row or some other record in the database. I find this might happen more often than people realize, and while your validation server-side might pass when you do the check, by the time you insert the record in the database it's possible that the user was deleted or some other instance causes your previously valid record to become invalid. This is why I find it completely necessary to use database validation and integrity constraints. With any decent sized traffic, concurrent connections have to be expected. MyISAM doesn't currently support relationships which (to me) makes it unusable as a reliable database source. Just my two cents.