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.
