"Lester Caine" wrote in message news:51d6987c.9050...@lsces.co.uk...
Tony Marston wrote:
I was designing and building database applications before relational
databases
became popular, and in those old hierarchical and network databases there
were
no such things as foreign key constraints. Database integrity had to be
handled
in the code, which is what we did. When I started working with relational
databases there were features of the language, such as FK constraints,
which
were rarely used even though they were there. When I started working on
web
applications the predominant database was MySQL, and the vast majority of
ISPs
would only offer MyISAM and not Innodb, and MyISAM does not support FK
constraints.
This was my point about MySQL ... what database were you using before MySQL
came along?
I used ORACLE and SOLID, and before that Hewlett Packard's IMAGE (network
DB) and Data General's INFOS (hierarchical DB).
There are some programmers who complain that without foreign keys being
defined
in the database, how do you recognise relationships to build into SELECT
statements? Simple. Every primary key is in the format ‘<tablename>_id’,
so if
you see this format in a table, and <tablename> is a different table,
then it’s
a foreign key. This is actually easier to identify than having to look in
a
separate place.
Remember that there is a difference between a foreign key and a foreign
key
constraint. Foreign keys can exist without constraints, so saying that
you
cannot use a field as a foreign key in a SELECT statement without having
a
constraint defined in the database is just plain wrong.
Certainly in some instances then managing everything in code makes sense.
Cross database working for instance. So everything works the same for each
engine. But the main advantage of adding constraints in the database is
that it protects the foreign entries from being deleted while they are
still in use. One of the problems I end up having to recover is where a
hickup in the PHP or user side of things has deleted an entry, or more
usually the whole table! In this case a properly implemented constraint
would have prevented the problem. While using a constraint to manage the
deletion of detail elements has a place in reducing traffic between program
and database, the protection of detail elements is the more useful reason
for maintaining them.
In my framework I define relationships and any constraints in my data
dictionary, and these are enforced by a standard module in the framework.
This means that any RESTRICTED constraints can be detected in the code
without executing a DELETE and having it abort.
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
