[Tfug] "Ordering" tuples in a table

[Bexley Hall]

Hi Dave,

> What you're talking about is commonly referred to as
> 'referential integrity'. Most databases have a

Yes.

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

Yes.  I can prevent the data from being inserted (modified, etc.)
without the required references existing.  What I am trying to 
get at is how do I force the *user* to always present data to the 
RDBMS in an order that follows these rules?

E.g., if you were typing in parent/child information, you
wouldn't intuitively *know* that you have to enter parent
data before child data.  That's a consequence of the way the
data has been reprsented (implementation issue).

So, what I do is create a new entry for "mom" *if* you give
me data for her as "child's" parent -- and then *hope* you
come around to eventually filling in the details of *that*
record at a later time.  (the way I do it currently is
pretty involved since I need to note those records that
were artificially created so that they aren't persistent 
*if* you happen to create yet another "new" record to replace
it)

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

Yes, that's what I do on INSERT (and DROP) presently.
But, mainly to provide a cleaner interface to the
database than the typical SQL error messages, etc.
 
> 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.

Yes, I think there are mechanisms in PostgreSQL that let me work
around that for "reloads".  The more insidious problem is handling
these references without forcing the *user* to enter data in
a specific order (think about it... you can come up with lots
of chicken/egg scenarios)

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