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.
