Lynna,
> Damn. Calculus was a long, long time ago for me, but I thought this sounded
> like something that *should* be possible. However, "should" does not mean
> "is". :-/
Check out www.dbdebunk.com. Fabian Pascal, CJ Date and others are developing
a "post-SQL" relational database; maybe someday it will be commercially
viable.
> They're fundamental to using the referential integrity feature of
> PostgreSQL, right? Or so it would appear to me, anyway... FWIW, the book
> ("Beginning Databases in PostgreSQL" by Richard Stones & Neil Matthew) does
> state that they're important, but they only go over the basic aspects of
> them (adding the REFERENCES constraint to tables or columns and a couple of
> options you can include with it) and say that their *full* potential is
> advanced, so I did at least get to learn a little bit about them. But more
> would have been better.
Oh, that makes sense then.
> It's not necessarily too much for me -- I generally like doing things the
> harder but more elegant way rather than the easier but sloppier way. But in
> this particular case, the decision is complicated by the fact that there
> are hundreds of existing records needing to be imported into the database
> once I get the structure finalized, all of which have existing ID numbers
> and existing relationships between them. So if I let PostgreSQL renumber
> all the records according to a new sequence, I'd have to manually recreate
> all the links between them, which given there are something like 1300
> records in total, would seriously suck. So I think I'm stuck with separate
> join tables.
Not necessarily:
-- The re-keying is optional. That is, it makes your solution more elegant
and foolproof, but is not absolutely required to do a distributed key. You
can handle the distributed key with overlapping ids by having your index on
the "id" and "type" fields combined.
-- You can re-key during conversion using a "was-is" table:
1. make a table with old_key INT, new_key INT;
2. select all of the current ids into old_key;
3. use procedural code to populate new_key with sequential values;
4. reference this was-is table for converting all child tables.
5. When done SETVAL your sequence to the highest value in any of the was-is
tables.
--
Josh Berkus
Aglio Database Solutions
San Francisco