Indeed.
I was just wondering why not make all constraints deferred?
What would be the problem with that?
For example NOT NULL constraint is checked before even the BEFORE
trigger is invoked.
Sometimes this can cause problems because the trigger would provide the
value but now it can't.
I am just wondering what bad consequences would it have to do all the
constraint checking at the transaction boundary.
I know it would have a lot of good effects.
- Rami
Dario Fassi wrote:
Hi Rami,
I think this schema would not work because DEFERRED constraints are
not supported yet.
A deferred constraint will be checked at commit time (at least not at
Statement.execute time) to let the rest of transaction the opportunity
to satisfy all involved deferrable constraints.
See this:
http://www.h2database.com/html/grammar.html#referential_constraint
"Defines a referential constraint. If the table name is not specified,
then the same table is referenced. |RESTRICT| is the default action.
As this database does not support deferred checking, |RESTRICT| and
|NO ACTION| will both throw an exception if the constraint is
violated. If the referenced columns are not specified, then the
primary key columns are used. The required indexes are automatically
created if required. Some tables may not be referenced, such as
metadata tables."
regards,
Dario
El 24/11/10 19:43, Rami escribió:
Hi,
Let's assume a db with 2 tables.
CREATE TABLE A(ID IDENTITY, FK BIGINT);
CREATE TABLE B(ID IDENTITY, FK BIGINT REFERENCES A(ID));
ALTER TABLE A ADD CONSTRAINT FOO FOREIGN KEY(FK) REFERENCES B(ID);
So A refers to B and vice versa.
Now I would like to insert a row into A and B.
row 'a' refers to row 'b' and vice versa.
SET AUTOCOMMIT OFF;
INSERT INTO A(ID, FK) VALUES(1, 1);
INSERT INTO B(ID, FK) VALUES(1, 1);
COMMIT;
At the moment these statements produce errors because the foreign key
constraint (and other constraints eg NOT NULL etc.)
are checked at the statement boundary.
It turns out there is no way to insert these rows (other than turning
referential constain checking off
in the whole database for all connections but that is obviously not
good for consistency.)
I propose that all constraints would be checked at the transaction
boundary.
So within a transaction the database could be in an inconsistent
state but only for
that one transaction. And when the changes of the transaction are
published to other
connections after a succesful commit data would be in consistent
state because
all checks for the changes have been made.
Does someone see any problems to consistency with this approach?
Would this be a big change?
- Rami
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
