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.

???

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

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.

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

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

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

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

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.

• 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.

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.

No - I'm quite happy with you using selects here

Mike

right, i'm done with this thread

good luck to syntaxerror, the original poster

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

Mike, errors are GOOD things. 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
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

Wow. That's all I can say to that. Wow.

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

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)

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