Deferrable constraints

["Williamson, Nick" <ni...@mentor.com>]

Hi all,
 
I'm migrating an Oracle schema to the Derby environment. In Oracle, all
my FK constraints are defined as INITIALLY DEFERRED DEFERRABLE, which
means that the validation checking isn't performed until commit time. If
I have parent table "A" and child table "B", I can create a row in table
B first, then create a row in table A (in the same transaction) and the
commit will be successful. Derby doesn't support INITIALLY DEFERRED
DEFERRABLE and the validation seems to be performed immediately by
default. Is there a way to over-ride this behaviour and emulate what I
had in my Oracle environment?
 
TIA
Nick

Re: Characters and bytes

["Bernt M. Johnsen" <Be...@Sun.COM>]

>>>>>>>>>>>> Williamson, Nick wrote (2007-06-19 11:09:34):
> Hi all,
> 
> In Oracle, you can specify character columns widths in bytes (the
> default) or characters. The advantage of specifying column width in
> characters is that you can attempt to store - say - 10 multi-byte
> characters in a VARCHAR(10) column without getting an error.
> 
> Derby doesn't seem to have an equivalent clause in the CREATE TABLE
> statement. What does it default to, and can the default be
> overridden?

In Derby the length of CHAR(n) and VARCHAR(n) is in caharcetrs, not in
bytes.  Java (and therefore in Derby) characters are Unicode, so
VARCHAR(10) will hold up to 10 Unicode characters. They will be
encoded in what the Java specification calls "Modified UTF-8" which
will be 1 to 3 bytes.

(You might run into problems if you have characters outside what is
called the "Basic Multilingual Plane", but that will only apply if you
deal with historic scripts like Cuneiform or Egyption Hieroglyphs etc)

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

RE: Characters and bytes

["Williamson, Nick" <ni...@mentor.com>]

> Derby's varchar(10) will store 10 Unicode (multi-byte) characters.  
> Are you saying you are getting an error when you attempt to do this?  
> I don't see an advantage to specifying bytes when all Derby data is 
> stored in a modified Unicode format.

Hi Stanley,

No, I'm not getting errors in Derby. I'm just used to getting them in
Oracle, and solving them by specifying width in characters! As Derby
doesn't have that option, I was concerned that Derby's column widths may
be in bytes. Now I know it's all character-bases, I have no worries.

Regards,
Nick

Re: Characters and bytes

[Stanley Bradbury <St...@gmail.com>]

Williamson, Nick wrote:
> Hi all,
>
> In Oracle, you can specify character columns widths in bytes (the
> default) or characters. The advantage of specifying column width in
> characters is that you can attempt to store - say - 10 multi-byte
> characters in a VARCHAR(10) column without getting an error.
>
> Derby doesn't seem to have an equivalent clause in the CREATE TABLE
> statement. What does it default to, and can the default be overridden?
>
> TIA
> Nick
>
>   
Hi -
Derby's varchar(10) will store 10 Unicode (multi-byte) characters.  Are 
you saying you are getting an error when you attempt to do this?  I 
don't see an advantage to specifying bytes when all Derby data is stored 
in a modified Unicode format.

Characters and bytes

["Williamson, Nick" <ni...@mentor.com>]

Hi all,

In Oracle, you can specify character columns widths in bytes (the
default) or characters. The advantage of specifying column width in
characters is that you can attempt to store - say - 10 multi-byte
characters in a VARCHAR(10) column without getting an error.

Derby doesn't seem to have an equivalent clause in the CREATE TABLE
statement. What does it default to, and can the default be overridden?

TIA
Nick

Re: Deferrable constraints

[David Van Couvering <da...@vancouvering.com>]

Since Derby doesn't support it, I can't think of an automated way to
do this.  But you could probably emulate this by removing the foreign
key constraint entirely and then using insert/update triggers to
enforce consistency.  I'm pretty sure that's how we did it in the old
days at Sybase before the days of foreign key constraints.

David

On 6/14/07, Williamson, Nick <ni...@mentor.com> wrote:
>
>
> Hi all,
>
> I'm migrating an Oracle schema to the Derby environment. In Oracle, all my
> FK constraints are defined as INITIALLY DEFERRED DEFERRABLE, which means
> that the validation checking isn't performed until commit time. If I have
> parent table "A" and child table "B", I can create a row in table B first,
> then create a row in table A (in the same transaction) and the commit will
> be successful. Derby doesn't support INITIALLY DEFERRED DEFERRABLE and the
> validation seems to be performed immediately by default. Is there a way to
> over-ride this behaviour and emulate what I had in my Oracle environment?
>
> TIA
> Nick