We got a little dispute in the FKEY project :-)
In section 11.9, the SQL3 draft explicitly discribes what to do for referential actions ON DELETE and ON
UPDATE. First there seems to be an incompatibility between SQL3 and SQL-92. While Date describes and Oracle
implementsNO ACTION to raise an exception if a PK delete leaves an unsatisfied foreign key, the SQL3 specs
explicitlydefine that behaviour for the RESTRICT action.
Second, there's absolutely nothing said about anything to do for NO ACTION in SQL3. Thus, our current
implementaion in fact doesn't do anything meaningful. That makes it totally legal, to delete a PK leaving an
unsatisfied FK behind, resulting in an in fact violation. And NO ACTION is the default if no referential
actions given explicitly in the schema.
Don Baccus now suggested, to interpret NO ACTION as "if it would result in a violation, then silently
rollback this update for the PK row in question". Not to speak about the technical problems arising from an
attemptto do so, but as said, such a behaviour is nowhere mentioned in the SQL3 draft. OTOH it would close
thepossible violation hole in our implementation of FOREIGN KEY.
What do others think about it? We need a decision urgent, or going for the suppress/rollback will cause a
release delay, definitely.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #