db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: [PATCH] Referential Constraints
Date Wed, 01 Jun 2005 16:51:25 GMT
Jack Klebanoff wrote:

> The attached patch fixes a problem that Derby had with conflicting
> referential constraints. Consider the following DDL:
> create table t2( ref1 int references t1(id) on delete cascade,
>                        ref2 int references t1(id) on delete set null)
> If both the ref1 and ref2 columns of the same t2 row refer to the same
> t1 row and that t1 row is deleted then the two referential constraint
> actions conflict. One says that the t2 row should be deleted, the other
> says that the ref2 column should be set to null. According to the
> SQL2003 spec an exception should be thrown when the t1 row is deleted.
> That is what Derby does after the attached patch is applied.
> Without the patch Derby disallows the DDL statement that creates a
> constraint whose action may conflict with other constraint actions. This
> is a mistake. Derby cannot tell at DDL time whether there will actually
> be a conflict. Derby rejects the DDL statement in the example above.
> However if none of the t2 rows refer to the same t1 row then there is no
> conflict. Derby rejects some DDL that will work perfectly well. As, I
> said above this is contrary to the SQL standard which requires that the
> checks be made at DML execution time not at DDL execution time.

To avoid discouraging future or current contributors I would not
describe the current behaviour as a 'mistake' or 'contrary to the SQL
standard'. Your patch is an improvment that implements more of the SQL
standard. In the open source world I would just say the previous
contributor implemented what scratched their itch, and decided not to or
didn't need to solve this issue. Though they did address it in some way
by disallowing such constraints, which was a much better solution than
allowing them with incorrect behaviour (which would have been contrary
to the SQL standard).

I would strongly encourage any contribution that partially implemented a
feature either as part of incremental development or because that's all
the contributor is interested in. It should not be seen as a mistake if
only part of SQL standard is implemented. Though any runtime (DML/Query)
behaviour that is allowed and that *is* contrary to the a standard would
need to be addressed.


View raw message