Temporarily disable restraints on a table?
Thom Pischke
the latest release. A few questions:
* Is there anyway to disable it for a single column?
* If I disable it for a single table, is there a way to turn it back
on in such a way that existing data will be revalidated?
* How will the previous use case perform (when re-enabling a
constraint for a large table, re-checking existing data)
My Use case is this:
We have a circular constraint, where certains rows of a table may
reference other rows. In the same table, there is a second such
constraint, on a different column. These circular references are
simply too much for Hibernate to handle intelligently, so we currently
handle it by dropping the constraint, then reinstating it after a
cascading save or delete.
This works pretty well, but gets kind of complicated in code, since
the constraint name has to be looked up, and added and deleted. This
new feature could be handy if I could just flip off constraint
checking for the affected table, then turn it back on afterward in
such a way that the table would be re-verified to ensure integrity for
all changed rows. This would keep hibernate from tripping over the
constraint without putting the database integrity at risk. It would
just defer the integrity check until after the save/delete is
complete, and just before transaction commit.
Thom Pischke
constraint on a column would automatically recheck data integrity for
all existing data. Is this a safe assumption, or have I opened a big
hole in my database with this technique?
Thom Pischke
On Aug 10, 2:25 pm, "Thom Pischke" <thom.pisc...@gmail.com> wrote:
> Something just occurred to me: I had assumed that re-instating a
> constraint on a column would automatically recheck data integrity for
> all existing data. Is this a safe assumption, or have I opened a big
> hole in my database with this technique?
>
Thomas Mueller
> I had assumed that re-instating a
> constraint on a column would automatically recheck data integrity for
> all existing data.
See http://www.h2database.com/html/grammar.html#sql23
'Enabling it does not check existing data.'
> > > * Is there anyway to disable it for a single column?
No
> > > * If I disable it for a single table, is there a way to turn it back
> > > on in such a way that existing data will be revalidated?
No
> > > * How will the previous use case perform (when re-enabling a
> > > constraint for a large table, re-checking existing data)
You would have to do that manually. Sorry...
> > > defer the integrity check until after the save/delete is
> > > complete, and just before transaction commit.
Some databases support a feature called 'deferred contraint checking',
however H2 does not yet support it. There is already a feature request
(Deferred integrity checking (DEFERRABLE INITIALLY DEFERRED)), I will
move it up. However it will take some time until this is implemented
(unless, somebody else wants to implement it of course).
Thomas
Thom Pischke
So it looks like I have a problem, since re-instating a constraint does not check existing data.
So when you say manually, I assume you mean writing an SQL statement to search for records violating the constraint in question. I suppose that wouldn't be difficult for single foreign key constraints.
Something like this maybe?
select id from table a where a.self_referencing_fk_1 not in (select id from a) or a.self_referencing_fk_2 not in (select id from a)
This query would probably execute the subquery twice I suppose, so maybe you can think of a better way to put this query together?
Thom Pischke
Thom Pischke
Thomas Mueller
> but what about when they are dropped and added?
You are right, the documentation doesn't say that. Currently, only
unique constraints are checked (because a unique index is added).
According to the spec, other constraints need to be checked as well.
So this is a bug (undocumented, non-standard behavior), not a feature
request. I will fix this for the next release.
How to check: Let's say there is a constraint
CREATE TABLE A(ID INT PRIMARY KEY, SK INT);
INSERT INTO A VALUES(1, 2);
ALTER TABLE A ADD CONSTRAINT AC FOREIGN KEY(SK) REFERENCES A(ID);
The following statements would need to be executed (currently manually
after creating the constraint):
SELECT COUNT(*) FROM (SELECT SK FROM A ORDER BY SK) CHILD
WHERE NOT EXISTS(SELECT 1 FROM A PARENT WHERE PARENT.ID=CHILD.SK);
If the result is 0 everything is ok, otherwise not. It looks complex,
but like this the indexes are used, so it should be a bit faster for
large tables.
However I'm not sure yet if I will implement it for ALTER TABLE SET
REFERENTIAL_INTEGRITY and SET REFERENTIAL_INTEGRITY as well in the
next release (maybe as an option, if it's not too much work).
Thomas
Thom Pischke
Thomas Mueller
It will be available in the next release. It is implemented and I will
check it in to SVN today.
Thomas
Thom Pischke
there was bad data already in our online database. This would be
possible because the database schema is changed quite often, usually
without exporting and importing to a new database. This means that
new constraints that are added to existing data would not have
resulted in data checks in the current engine (though it will in the
next release).
So what's the easiest way to verify the entire database? Would be
great to have a way to do this for an existing database, but if that's
not going to possible anytime soon, I assume the protocol would be to
create a new empty database with all required constraints, then
migrate the data?
On Aug 14, 7:22 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> It will be available in the next release. It is implemented and I will
> check it in to SVN today.
>
> Thomas
>
> On 8/14/07, Thom Pischke <thom.pisc...@gmail.com> wrote:
>
> > Ok, implemented the integrity check as you suggest above. Thanks! Will
> > watch for the patch to then engine in the next release.
>
Thomas Mueller
> So what's the easiest way to verify the entire database?
> create a new empty database with all required constraints,
> then migrate the data?
Yes, I think that would be the easiest solution.
First, run SCRIPT NODATA TO 'schema.sql' to create the schema.
Then run SCRIPT TO 'all.sql' to create everything.
Use RUNSCRIPT FROM 'schema.sql'.
Afterwards RUNSCRIPT FROM 'all.sql'
Thomas
Thom Pischke
Trying this:
private void importFile(File filePath) throws SQLException {
final String sql = "RUNSCRIPT FROM '" +
filePath.getAbsolutePath() + "'";
final PreparedStatement stmt =
connection.prepareStatement(sql);
try {
stmt.execute(sql);
} finally {
stmt.close();
}
}
and getting this:
Caused by: org.h2.jdbc.JdbcSQLException: Feature not supported
[HYC00-56]
at org.h2.message.Message.getSQLException(Message.java:84)
at org.h2.message.Message.getSQLException(Message.java:66)
at org.h2.message.Message.getSQLException(Message.java:105)
at org.h2.message.Message.getUnsupportedException(Message.java:109)
at
org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:
242)
at sb.util.db.Database.importFile(Database.java:152)
at sb.util.db.Database.build(Database.java:132)
On Aug 16, 10:37 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thom Pischke
final String sql = "RUNSCRIPT FROM '" + filePath.getPath() + "'";
final Statement stmt = connection.createStatement();
try {
stmt.execute(sql);
} finally {
stmt.close();
Thomas Mueller
As you already found out, calling execute(sql) on a _prepared_
statement is not legal according to the JDBC spec. I will change the
exception message for this case to:
"This method is not allowed for a prepared statement; use a regular
statement instead."
Thomas