db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: "generated by default" question
Date Tue, 30 May 2006 19:45:01 GMT
>>>>>>>>>>>> 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

Mime
View raw message