On Mon, 11 Nov 2013 18:35:31 +0400
dd <durga.d...@gmail.com> wrote:
> Can I conclude this way: Foreign keys works pretty well when
> application deals with parent keys only. But, application may need to
> execute more queries when dealing with child key/tables.
Constraints express rules that the DBMS enforces for you. If you can
express the constraint
if table B contains no references to a row in table A,
delete that row
then you can save yourself programming effort and rely on the DBMS.
SQLite seems to support such a declaration with the CASCADE keyword,
but it's undocumented on
http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause in any
case wouldn't really do what you want.
The SQL standard says ON DELETE CASCADE means referencing rows are
removed when a referenced row is deleted. That is,if B has a foreign
key reference to A, B rows are deleted when the A row is deleted.
That's like removing all the tracks when deleting the artist, not
removing the artist after deleting the last track.
There's actually a good reason for that, and it's a good illustration
of what distinguishes application logic from database consistency.
Declared referential integrity (DRI) -- and triggers, something often
overlooked on this list -- exist to enforce referential integrity: to
ensure that relationships that must exist do exist. Relationships that
are permitted but not required, OTOH, can't be enforced.
You've declared a rule that an Artist is an integral feature of a
Track, that every Track must have an Artist. Another way to put that:
an Artist has a set of Tracks, and each Track is a member of a set
belonging to an Artist. Sets can be empty, though, and nothing
prevents an Artist from having no Tracks. Not much of an artist,
perhaps, but when you're talking about art you can't expect things to
be logical.
The DBMS doesn't know tracks from artists. It only enforces the
existence of a set before it allows members of that set; it make no
sense to have members belonging to no set.
That's why CASCADE works the way it does. It supports the requirement
that all members belong to a set, not that all sets have members,
because the latter has no mathematical basis.
In the application, the business (you) may decide you're not
interested in tracking empty sets, however valid they may be in
database terms. That's fine; that's a business decision, and that's
what application logic is for. An application regularlizes and
codifies an orgainzation's behavior. Only the organization can decide
what information to keep in the database. If you don't want to track
artists without tracks -- or hotels in Rhode Island, for that
matter -- that's up to you. The DBMS can't force you to keep something.
It only helps you ensure that what you do keep is internally
consistent.
So: deletion of a row representing an artist with no tracks is a
choice, just as insertion of that row was in the first place. The
database remains consistent with or without the row. It will be little
missed nor long remembered. If that should be done automatically,
automate it in the application, and rest easy.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
