I have 2 tables in a project that uses HSQLDB, they are called Staff and Staff2.
Staff has Id PRIMARY KEY, as written here:
s.execute("CREATE TABLE Staff(Id varchar(10) PRIMARY KEY, FirstName varchar(30), LastName varchar(30), Department varchar(255))");.
Staff2 has Id FOREIGN KEY referencing the PRIMARY KEY of Staff, as written here:
s.execute("CREATE TABLE Staff2(Id varchar(10), TeachingCourse varchar(30), UserName varchar(255), FOREIGN KEY(Id) REFERENCES Staff(Id))");.
Now, It seems that because I did not drop those keys in the first place, I can't drop the tables. When I'm executing the program it shows the following error:
exception thrown:
java.sql.SQLException: Table is referenced by a constraint in table SYS_REF_SYS_FK_272_275 table: STAFF2 in statement [drop table STAFF]
When I'm trying to drop the keys independently prior to dropping the table, I'm getting other errors:
When I'm doing:
s.execute("ALTER TABLE Staff DROP PRIMARY KEY");
The result is:
exception thrown:
java.sql.SQLException: Attempt to drop a foreign key index: SYS_IDX_46 in statement [ALTER TABLE
Staff DROP PRIMARY KEY]
And when I'm doing:
s.execute("ALTER TABLE Staff2 DROP FOREIGN KEY Id");
I'm getting:
exception thrown:
java.sql.SQLException: Column not found: FOREIGN in statement [ALTER TABLE Staff2 DROP FOREIGN]
It is possible that I used Add CONSTRAINT somewhere along the way and did not close it, but DROP CONSTRAINT doesn't work as well (produces exception thrown:
java.sql.SQLException: Constraint not found ID in table: STAFF in statement [ALTER TABLE Staff DROP CONSTRAINT Id]),
and I couldn't understand how to find the constraints, as well as what exactly is the meaning of these constraints and if I should find them.
Now I'm stuck and can't continue, as the tables can't be dropped until the keys are dropped.
Closing the HSQLDB server and restarting it did not close the tables.
I could also use an advice regarding how to start it all over again and this time drop the keys before dropping the tables from the first time.
You can drop the tables but you have to do it in reversed order. STAFF2 depends on STAFF so you cannot drop STAFF first. You don't have to remove constraints separately. Just execute following two commands:
DROP TABLE STAFF2;
DROP TABLE STAFF;