Hello, A while back I wrote about a problem with using "DELETE FROM <TABLE>" with very large tables. It can be very slow and memory intensive. Hence, I tried using "TRUNCATE TABLE". The problem is that some of my tables have referential constraints, thus, I couldn't truncate them.
Now, I understand the dangers, but I think that, when setting "SET REFERENTIAL_INTEGRITY FALSE", this check should be ignored. In my understanding, that's what this flag should mean. In the mean while, I'm using the following technique, I thought I'd post it for the benefit of the group. Any feedback is welcome. 1. Get the constraints names: SELECT FK_NAME FROM INFORMATION_SCHEMA.CROSS_REFERENCES WHERE PKTABLE_NAME='<TABLE>' 2. Get the SQLs for those constraints: SELECT SQL FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_NAME IN (SELECT FK_NAME FROM INFORMATION_SCHEMA.CROSS_REFERENCES WHERE PKTABLE_NAME='<TABLE>') 3. Drop each constraint that was returned in step 1: ALTER TABLE <TABLE> DROP CONSTRAINT <NAME> 4. Truncate the table: TRUNCATE TABLE <TABLE> 5. Recreate the constraints by executing the SQL statements I got in step 2. Thanks, Zviki -- 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.
