Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Apr 2006
    Posts
    157

    Unanswered: How critical are foreign keys and constraints

    Hi everyone,

    I have been developing database systems for a little over a year now with 3 ms access projects and partial involvement on an AMP project.

    Something i've been accustomed to, when developing projects is that, everything changes. Entities evolve... no matter the preparation( like i've read once about different approaches to system development cycle ).

    Cut to the chase:

    How important would it really be for me to implement foreign keys, constraints
    ... cascades on update, and on delete?

    Based on experience, these are things that i have to deal with on the front end. So why should I even bother implementing constraints at the back, if I have to make sure that erroneous entries don't happen up front anyway?

    As for cascaded updates, or deletes... I don't even use them, because I always need archive version of records.

    ???
    Only quitters quit!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by syntaxerror
    So why should I even bother implementing constraints at the back, if I have to make sure that erroneous entries don't happen up front anyway?
    From my 15+ years of experience: you can not make sure in the front end that no erroneous entries appear.
    So guess what my recommendation is: use the database as a RDBMS and not as a flat file system (because that's what you do when you don't apply the basic rules of the relational model)

    You might get away with this approach with a small Access front-end where only your application accesses the data, but as soon as we are talking enterprise wide apps and databases, proper constraints and foreign keys they are absolutely mandatory.

    But then what if your users find out how to manipulate the data say through Excel?

    Believe me: I have spent more time fixing data issues in databases that did not apply a proper relational model (because their creators where sure they could handle that in the front-end), then I spent maintaining those constraints

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Foreign keys are not critical to any form of DBMS, arguably they are not required for a relational DBMS.. after all MySQL ran for many years without obvious keys.. nothing is a free lunch......

    imposing constraints, foreign keys comes at a cost, esepcially in inserts or changes to the data

    running without constraints can make a db faster.. thats was partially the MYSQL on ISAM argument.. if you are not doing much data changes or inserts then plain vanilla ISAM was blinding fast. But that means that you the developer have to make sure that you alwasy carefully consider the impact of any changes on the db.. and manually enforce the constraints or business logic.

    Personally in my view no sane developer or DBA would not use the constraints imposed by foreign keys or whatever in the db. its exactly like insurance... why have an expensive car or whatever and not insure it..... yes you cna drive round in your car, yes you may be a safe carefull driver but you just don't know what herberts are out there, and what they may do to your precious data. one thing you can guarantee is that if the data is compromised its not going to be the developer who forgot to programatically impose the business logic its going to be you the system architect.

    we are in a slightly gray area, in as much as some / many / who cares OO exponents who believe that OODB's are the right way to impose such business logic.. I like the idea, but in practice right know OODB's are not powerfull enough for high volume commercial transactions. Some OO practioners suggest that the business logic should be in the object not the db.. again providing the OO developer knows their stuff it shouldnt be a problem but all you need is one rogue OO app to nuke the data

    Personally in an OO environment right now Id want to use belt and braces... whatever the OO class requires is fine.. but Id also want to keep the foreign key constraints and other stored procedure processing to ensure the integrity of the data. Id far rather take the performance hit, than let my schema be exposed to corruption however caused.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by syntaxerror
    How important would it really be for me to implement foreign keys, constraints
    ... cascades on update, and on delete?
    foreign keys -- absolutely mandatory if the database is being updated (i.e. if it's more than a read-only database)

    constraints -- the more the better, if the database is being updated

    ON UPDATE CASCADE -- necessary if you use natural keys, although ON UPDATE RESTRICT makes a lot of sense too

    ON DELETE CASCADE -- only if the business rules allow it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    So why should I even bother implementing constraints at the back, if I have to make sure that erroneous entries don't happen up front anyway?
    The problem of doing all the checks at the front end is that there are usually many ways for data to get into the system ie screens / feeds / SQL etc - this means you'll have to repeat all your checks at each entry point. Having your checks in the database means that bad data can't ever creep in.

    I can actually feel a warm glow from Rudy just for saying this

    How critical are foreign keys and constraints?
    FK's aren't all sweatness and light though ...

    Hidden functionality: You might have a simple bit of SQL ie update Users set loc="Frankfurt" where id=1234" which is syntactically correct but raises errors due to the "hidden functionality" of FK's and triggers. This hidden functionality can now deliberately cause the simple sql to raise errors.

    Extra complexity: Because even the most inocuous SQL can now raise errors we now need to add complex error checking to all SQL in your code. Soon most of your code becomes error checking and the system becomes more complex to read, understand and maintain.

    Design limitations: Using FK's puts some minor constraints on your design - I personally like having a single lookup table (OTLT) in my databases rather than 10 or 20 individual tables. I accept that most people hate OTLT mainly because FK's can't be used on this table but it does make the overall design cleaner. Whether you like OTLT or not - this does imply that FK's impose limitations on your design.

    One solution: is to use sprocs for all the actions in the database ie add_new_user and use functions to provide data checks (ie is_loc_valid( "Frankfurt" ) ). I don't allow updates, inserts and deletes from SQL by the 3GL progs. The advantages to me are:
    • simpler 3GL code with a single sproc call rather than tonnes of SQL with all attendant error checking
    • quicker to write due to above
    • easier to maintain due to above
    • no hidden functionality
    I can now feel Rudy spitting his coffee out all over his screen and no doubt I'll regret raising any of the above but I believe there are issues with FKs and triggers in databases

    Mike

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the "issues" you mention, mike, aren't issues at all

    hidden functionality -- do i really want the database to check if i'm adding an order transaction for a customer that doesn't exist? why can't i just add the transaction? i'll figure out the customer later, using a stored procedure

    extra complexity -- what? the customer doesn't exist? okay, we need a simple flag here that says this order is for an invalid customer, maybe we can put that flag in the one true lookup table (codetype: orders, code: valid, values: true/false) -- that certainly minimizes the database complexity, doesn't it

    design limitation -- no need for special processing worries here, let's store all the product attributes in the one true lookup table, in fact, why bother with order, product, customer tables anyway, that will really limit the design possibilities, let's use a lookup table for all the data

    One solution -- while we're at it, we need to use sprocs for all retrievals, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    hidden functionality -- do i really want the database to check if i'm adding an order transaction for a customer that doesn't exist? why can't i just add the transaction? i'll figure out the customer later, using a stored procedure
    We definitely want to check the data is correct before inserting it into the database. The question is whether we do it by using multiple inserts etc that rely on FKs (and triggers) or an explicit call to a sproc that does the integrity checking there. To ensure the sproc is called we need to disallow all updates, inserts and deletes.
    • FK (and triggers) are good because they can't be sidestepped but the fact that they're used at all is hidden from the user / programmer.
    • Sprocs are explicitly called and so are not hidden.


    extra complexity -- what? the customer doesn't exist? okay, we need a simple flag here that says this order is for an invalid customer, maybe we can put that flag in the one true lookup table (codetype: orders, code: valid, values: true/false) -- that certainly minimizes the database complexity, doesn't it
    • FK (and triggers) raise errors in the most innocent SQL code which means all SQL needs to be fully error checked. If we create a new user that requires adding data to 3 tables then you'd have the 3 inserts plus all error checking.
    • Using a sproc means just a single call and perhaps a return string "OK" or "No such location as Frankfurt" etc. There's no need to raise an error as such. This seems simpler to me but YMMV.


    design limitation -- no need for special processing worries here, let's store all the product attributes in the one true lookup table, in fact, why bother with order, product, customer tables anyway, that will really limit the design possibilities, let's use a lookup table for all the data
    OTLT was just used as an example of a limitation in database design caused by using FK. I wasn't suggesting that you should use OTLT. Seeing as FK stop us from using OTLT - this implies that FK introduce some limitations.

    One solution -- while we're at it, we need to use sprocs for all retrievals, too
    No - I'm quite happy with you using selects here

    Mike

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    Seeing as FK stop us from using OTLT - this implies that FK introduce some limitations.
    right, i'm done with this thread

    good luck to syntaxerror, the original poster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by mike_bike_kite
    Seeing as FK stop us from using OTLT - this implies that FK introduce some limitations.

    Originally Posted by r937
    right, i'm done with this thread
    I was simply trying to provide an example of a limitation from using FK - I wasn't re-opening the OTLT debate. If FK stop us from doing something (what ever that something is) then that is a fair example of a limitation.

    Mike

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Mike, errors are GOOD things. Corrupting your data with no warning is a BAD thing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Corrupting your data with no warning is a BAD thing
    Totally Agree - I just happen to use sprocs to ensure the data is valid. Mostly for the reasons listed above. So my code might be similar to
    Code:
    call add_new_user( ... )
    
    if return_str != "ok" then display the warning in return_str
    errors are GOOD things.
    Not so sure on that one - I hate to see errors anywhere even ones raised on purpose. Using sprocs I can generate more user friendly warnings that might perhaps give warnings on all the bad values entered in the record. Using FKs it will just blow up on the first failure.

    I'm not saying that using FKs is bad - I'm just saying that using sprocs instead of FKs does have a number of advantages. Obviously if you can't ensure that all data changes will go through your sprocs then these advantages will disappear.

    Mike

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mike_bike_kite
    I'm not saying that using FKs is bad - I'm just saying that using sprocs instead of FKs does have a number of advantages.
    Wow. That's all I can say to that. Wow.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The way that I see it, foreign keys are only as important as your data quality is... If you don't care if your data is clean, then foreign keys aren't necessary. If it matters that your data is correct, then foreign keys are required.

    The importance of the foreign keys is really something you as the DBA need to decide about your particular database... Sometimes the data just isn't very important, and then foreign keys don't matter much.

    -PatP

  14. #14
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan
    Sometimes the data just isn't very important, and then foreign keys don't matter much.
    But then, they don't "cost" much either, so I cannot perceive any reason not using FKs
    (cascading updates and deletes is another story though)

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I probably ought to have used a "smiley" in my previous post... If you care enough to save your data (in other words, put it in a database) then I think you ought to use foreign keys. If it doesn't matter to you if the data is correct or not, just use a random number generator and don't bother using the database!

    -PatP

Posting Permissions

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