Re: CREATE TABLE with REFERENCE

> >That's actually what I was saying.  Within a single transaction rather
> >than across multiple transactions.  If you have to go across multiple
> >transactions, there's no real point in having integrity constraints.
> >
> But why would you have to go accross multiple transactions, when you are
> inserting entries into the tables, that are related, and reference each
> other?

I have no idea.  I wasn't the original poster.

> >Do you know of a better way to handle multitable references?
> >
> >
> Sure.
> SET CONSTRAINTS DEFERRED;
> BEGIN;
> insert this
> insert that
> END;
>

That only handles single table references.

For example, I have a database with a "notes" table.  This table is used
to store annotations on ANY record within the database on ANY table.  I
use a single 64-bit sequence for every key within the database.  In fact,
in the GUI framework I ahve set up, in order to add notations to any
screen, I can just add the following code:

note_html($object_id);

and it will insert code to be able to add/update/remove notes on that
object.  So, my table NOTES has references to about 5 different tables so
far (it will probably grow with the application), all with the same
column.

There are actually numerous uses of this - I once started writing a paper
on building a generic database structure that could be reusable across
multiple applications, but never had the time to finish it.

Jon