Subject Re: [firebird-support] Re: Immediate constraints
Author Helen Borrie
At 04:57 PM 9/08/2005 +0000, you wrote:
>Thanks for quick answer. I'll try to describe my situation more
>precisely. I connect to embedded firebird instance through Jaybird
>JDBC driver. There are few actions performed:
>1. Create tables (about 300 of them).
>2. Alter those tables by adding basic constraints (pk, fk, unique).
>3. commit
>4. Fill them with a lot of data.
>5. commit
>On last commit i get FBSQLException:
>violation of FOREIGN KEY constraint "INTEG_904" on table "some_name"

This doesn't indicate "deferred constraints", it indicates that you tried
to insert a value into the foreign key column that didn't have a match in
the referenced table.

>If this db supports only immediate constraints, why do i get this
>exception at commit time, and not when inserting data?

You get this exception immediately the server tries to write the new record
version to the database, a step referred to by some as "posting". Some
interfaces combine post and commit into one step, using a feature called
"autocommit". So, even though you see the exception when you call Commit,
it's really occurring at post.

> It seems very
>weird... That is why i assumed that this db supports only deferred
>constraints (another reason: i had problems finding info about it in
>docs). What could be the reason?

The purpose of referential integrity constraints is to constrain incoming
data to protect referential integrity!! So, if you try to plug in a FK
value that has no "parent" in the referenced table, you get an exception
and you won't be able to proceed until you fix it. The same will occur if
you try to pass a Null - since Null, being a state and not a value, can
never have a parent.

You can make it so that a non-null value is allowed to be altered to Null,
by applying an action clause when defining the constraint - ON UPDATE SET
NULL, for example - but the constraint itself commits you to *inserting*
data that is already referenced in the parent table.

Each external standard imposes its own assumptions and rules on database
operations. JDBC is no exception - that's why you need DBMS-specific
drivers. You'll be in a better place to resolve your Java problems if you
post a new question to the firebird-java list, explaining what you need to do.