disable referential integrity constraint in derby

[Anjana Prakash <an...@actian.com>]

Hi,

Is there an option in derby to temporary turn off referential integrity constraint. This is needed when we do bulk import and post this process we would want
It to be enable.

Thanks,
Anjana.

Re: disable referential integrity constraint in derby

[Rick Hillegas <ri...@gmail.com>]

On 12/2/15 8:55 PM, Anjana Prakash wrote:
>
> Hi,
>
> Is there an option in derby to temporary turn off referential 
> integrity constraint. This is needed when we do bulk import and post 
> this process we would want
>
> It to be enable.
>
> Thanks,
>
> Anjana.
>
Hi Anjana,

If a constraint was declared DEFERRABLE, then you can defer its 
enforcement to commit time. See the Derby Reference Manual section on 
CONSTRAINTS: http://db.apache.org/derby/docs/10.12/ref/rrefsqlj13590.html

The following script shows this behavior:

connect 'jdbc:derby:memory:db;create=true';

create table primary_t( a int primary key );

create table foreign_t( a int references primary_t( a ) deferrable );

-- raises a foreign key violation because autocommit is on
insert into foreign_t values ( 1 );

autocommit off;

set constraints all deferred;

-- succeeds
insert into foreign_t values ( 1 );

-- raises a foreign key violation
commit;

However, this will not help you if you are using the builtin import 
procedures, which autocommit. You may want to arrange your import 
statements in dependency order so that primary key tables are loaded 
before the foreign key tables which reference them.

Hope this helps,
-Rick