Rod Taylor wrote:
>
>
>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;
>
>How does MSSQL deal with the above?#
>
>
It depends. Restricting FKs are generated silently, while ON DELETE
CASCADE will throw a message and refuse to create. MSSQL doesn't know
about deferred FKs; no chance to enter spuriously inconsistent data.
Still, using cyclic references is IMHO bad design style. I can't accept
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 :-)
Regards,
Andreas