> >CREATE TABLE a (col integer primary key);
> >CREATE TABLE b (col integer primary key);
> >ALTER TABLE a ADD FOREIGN KEY (col) REFERENCES b INITIALLY DEFERRED;
> >ALTER TABLE b ADD FOREIGN KEY (col) REFERENCES a;
> Still, using cyclic references is IMHO bad design style. I can't accept
They're extremely useful when you have normalized data and a very
expensive (but repeatable) process whose results you want to cache for
performance reasons. It enforces that original data and cache are both
added in a single transaction.
But you're right. Normally they're a bad idea.
> an exceptional case as reason to break *all* table's definition into
> pieces. The CREATE TABLE syntax shows that I'm probably not the only one
> thinking like this: it may include all constraint definitions as well.
>
> There might be discussions whether its better to script
> CREATE TABLE xxx ..;
> ALTER TABLE xxx ADD PRIMARY KEY ....;
> ALTER TABLE xxx ADD FOREIGN KEY ....;
> or
> CREATE TABLE xxx (...., PRIMARY KEY (..), FOREIGN KEY (..));
>
> I'd opt for the second version (a little formatted, maybe :-)
Well.. the second one will be much slower when the foreign keys verify.
Primary, unique constraints I'll buy in the create statement. Check
constraints and defaults are a little fuzzier.
Logic will be required to pull them out in the event they call functions
which depends on the table or we enable subqueries (assertion like
constraints) in them.