CODEWITHSUNDEEP
javasqlh2
user12511608
user12511608

Reputation:

Truncate Table with Referential Constraint

I have tables "Item" and "Category", where "Item" contains a foreign key constraint that references "Category" (i.e. every item has the name of a category element in its category-column). This constraint is called "CONSTRAINT_74E".

When the data is changed by the user, I need to overwrite the entire database. For this I use the TRUNCATE command. I tried to disable the constraint to truncate CATEGORY:

command = "truncate table ITEM;";
s.execute(command); //throws no exception

command = "alter table ITEM disable constraint CONSTRAINT_74E;\n" +
                "truncate table CATEGORY;\n" +
                "alter table ITEM enable constraint CONSTRAINT_74E;";
s.execute(command);

This yields the following exception:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "ALTER TABLE ITEM DISABLE[*] CONSTRAINT CONSTRAINT_74E; TRUNCATE TABLE CATEGORY; ALTER TABLE ITEM ENABLE CONSTRAINT CONSTRAINT_74E;"; expected "., ADD, SET, RENAME, DROP, ALTER";

SQL statement: alter table ITEM disable constraint CONSTRAINT_74E; truncate table CATEGORY; alter table ITEM enable constraint CONSTRAINT_74E; [42001-200]

What exactly do I need to do to make this work? I don't see how I would need to add SET/RENAME/etc. after DISABLE.

Upvotes: 0

Views: 1285

Answers (1)

Thomas
Thomas

Reputation: 88747

Of course you can't remove data that's being used as a foreign key as that would break referential integrity. You could try to remove the foreign key but adding it again would break if the keys are not the same - and if they are, why are you truncating in the first place?

Depending on your requirements you have a few options here:

  • don't truncate but use some other update mechanism (I'd prefer that but I don't know your requirements for truncating in the first place)
  • remove the foreign key constraint altogether and deal with missing/wrong keys
  • remove the foreign keys (i.e. the values) before truncating and if possible try to reinsert them after new data has been added

Update: since you're only asking for the syntax (at least for now), have a look at the H2 commands documentation.

There you'll see that disabling foreign key checks, aka referential integrity, would be done using this command:

ALTER TABLE ITEM SET REFERENTIAL_INTEGRITY FALSE

It seems as if disabling other constraints is not supported so you might have to drop and add them again.

Btw:

I don't see how I would need to add SET/RENAME/etc. after DISABLE.

Note that ...expected "., ADD, SET, RENAME, DROP, ALTER"; doesn't mean the statement parser expects the mentioned commands after DISABLE but instead of rename, i.e. DISABLE is not supported at this possition (if at all).

Upvotes: 0

Related Questions