Re: Self-referential records

On 1/24/10 8:43 AM, Ovid wrote:
> Assuming I have the following table:
>
> CREATE TABLE refers (
> id SERIAL PRIMARY KEY,
> name VARCHAR(255) NOT NULL,
> parent_id INTEGER NOT NULL,
> FOREIGN KEY (parent_id) REFERENCES refers(id)
> );
> I need to insert two records so that "select * from refers" looks like this:
>
> =# select * from refers;
> id | name | parent_id
> ----+------+-----------
> 1 | xxxx | 1
> 2 | yyy | 2
>
> The first record can't be inserted because I don't yet know the parent_id. The second record can be inserted after the first, but I since this is merely a large .sql file that I intend to shove into the PG, I'd much rather declare a variable in the script to get this done. I'm thinking something like the following pseudo-code:
>
> INSERT INTO refers (name, parent_id) VALUES ('xxxx', :id);
> SELECT id INTO :parent_id FROM refers WHERE name='xxxx';
> INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
>
> Obviously the above is gibberish, but hopefully it makes clear what I'm trying to do :)
>
> Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies associated with NULL values.

You could always remove the NOT NULL or the FOREIGN KEY constraints
during data load, then add them back on afterward.

If the problem is with everyday usage and not just data load, you
can still do this trick, since DDL can be transactionalized (is
that a word). Just start a transaction, remove the NOT NULL
constraint, add your new records, then update the parent_key as
appropriate, then add the NOT NULL back. If any point during the
process fails, just rollback the transaction. You may want to
set the isolation level to serializable, but I'm not sure if
that's necessary.

-Bill