[Tfug] "Ordering" tuples in a table

[Dave Thompson]

Bexley,

What you're talking about is commonly referred to as 'referential
integrity'. Most databases have a 'foreign key' constraint that will
automatically check the validity of a reference when a tuple is inserted or
updated. If the reference is bad, the statement will fail.

If you're using a database that doesn't support foreign key constraints
(SQLite, MySql MyIsam), you can often write a custom trigger function that
gets called on insert or update to enforce referential integrity.

Checking every reference key can be a significant performance hit when
inserting or updating many rows at once. If you're loading data from a CSV
or SQL file, it is very common to drop the foreign key constraint before
doing the insert/update operation, and then add it back after everything has
been inserted. This method will be a big performance boost for large data
sets, and you won't have to worry about the order of the data being
inserted/update.

If you're using a database that supports transactional DDL statements
(PostgreSQL, Oracle, etc), everything can be run inside of a transaction
block, and this method can be used safely, even with concurrent users. Other
databases, such as MySQL and SQLite do NOT support transactional DDL
statements, so using this method with concurrent users could be potentially
dangerous.

-Dave

On Mon, Oct 19, 2009 at 6:56 PM, Bexley Hall <bexley401 at yahoo.com> wrote:

> Hi,
>
> OK, I'm still trying to adapt my coding style to migrate
> code and data structures *out* of the executable and into
> an RDBMS.  And I keep stumbling over "little things" of
> which I must give up control in order to adopt these
> mechanisms.  :<  (dems da brakes!)
>
> In particular, I am trying to figure out how to
> adapt to the fact that tuples in the database are
> not ordered in the same sense that I can explicitly
> order them in a TEXT (or DATA) segment within my code.
> (yes, I know you can order them "on output" but the
> data themselves are not really "ordered" within the
> table -- forget indices, etc)
>
> E.g., the RDBMS doesn't see any difference in the
> following two lists:
>  Tom Martin
>  Bob Jones
>  Fred Williams
> vs.
>  Fred Williams
>  Tom Martin
>  Bob Jones
> And, this is "A Good Thing"!  :-/
>
> But, when building table driven code, you often
> implicitly (or explicitly) apply some ordering to
> the contents of your tables!  It allows you to
> later explot that ordering to simplify the
> algorithms that are *driven* from those tables!
>
> In one such table, I *guarantee* circular definitions
> can't exist by imposing the requirement that anything
> referenced by a definition must already be defined
> (i.e., must exist "earlier" in the table).  So, I
> can have:
>  x = 3
>  ...
>  y = x + 2
> but this rule prevents:
>  x = y - 2
>  ...
>  y = 5
> (i.e., because, at the time x is defined, y does not
> yet have a definition).  Otherwise, I could create
> anomalies inadvertently like:
>  x = y - 2
>  ...
>  y = X + 2
>
> I can force new entries to the (RDBMS) table to observe this
> rule by tying some code to the INSERT method such that any
> references the code can't resolve at INSERT-time are
> rejected.
>
> However, if the table is eventually *dumped*, there is
> no guarantee that rebuilding the table will cause the
> tuples to be re-INSERTed in the same order that was
> imposed on the original INSERTions.
>
> So, how do you do this?  Consider a record for a
> "person" having fields that specify name, birthdate, etc.
> In addition, fields that REFERENCE *other* records
> defining the "mother" and "father" for that "person".
> I.e., how do you refer to a mother who's record doesn't
> yet exist??
>
> (I figure *this* sort of thing has to come up "in the
> real world"!?)
>
> Thanks!
> --don
>
>
>
>
>
> _______________________________________________
> Tucson Free Unix Group - tfug at tfug.org
> Subscription Options:
> http://www.tfug.org/mailman/listinfo/tfug_tfug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://tfug.org/pipermail/tfug_tfug.org/attachments/20091019/73575528/attachment-0002.html>