SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign key contraints: good or bad?

    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:
    PHP Code:
    ALTER TABLE borrowed 
    ADD CONSTRAINT FK_borrowed 
    FOREIGN KEY 
    (employeeidREFERENCES 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?

  2. #2
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Netherlands
    Posts
    172
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    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?

    Quote Originally Posted by allspiritseve View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mastodont View Post
    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



    Quote Originally Posted by allspiritseve View Post
    Doesn't the app need to know these rules anyways?
    in general, nope
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  11. #11
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "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 ?

  14. #14
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    ... 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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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!

  17. #17
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    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.
    Adrian Schneider - Web Developer

  18. #18
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  19. #19
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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.

    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.

  20. #20
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kailash Badu View Post
    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.

  21. #21
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Kailash Badu View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  22. #22
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    "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.

  23. #23
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    706
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    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.

  24. #24
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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.

  25. #25
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •