>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-05-30 20:20:48): > I have not studied this well enough to conclude wether Derby's current > behaviour is compliant with the SQL 2000 spec or not. > > But there is nothing in the Derby charter that requires Derby to be > SQL 2000 compliant, so if you're right, Derby is not compliant, but > that's no bug, that's a feature, especially if it's documented.
Hmmm... thought "SQL 2000 spec" referred to MS SQL 2000 Server (As far
as I know, there is no SQL 2000 standard).
But as far as I read the SQL 2003 *Standard*, Derby behaves according
to the standard.
In the following example:
ij> create table t (i integer generated by default as identity primary key, n
varchar(10));
0 rows inserted/updated/deleted
ij> insert into t (n) values ('insert-1');
1 row inserted/updated/deleted
ij> insert into t (i,n) values (2,'insert-2');
1 row inserted/updated/deleted
ij> insert into t (n) values ('insert-3');
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'SQL060530093932950' defined on 'T'.
ij> insert into t (n) values ('insert-4');
1 row inserted/updated/deleted
ij> select * from t;
I |N
----------------------
1 |insert-1
2 |insert-2
3 |insert-4
3 rows selected
ij>
The third insert here should fail since the internal sequence
generator will generate a value which violates a constraint. The
transaction rolls back, but not the internal sequence generator. Just
as specified. The fourth insert will then succeed.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
signature.asc
Description: Digital signature
