Drop all constraints in Derby via JDBC

Question

How can I drop all constraints in a Derby database via JDBC?

Answer 1

You could query the system tables SYS.SYSCONSTRAINTS, SYS.SYSTABLES and SYS.SYSSCHEMAS to get all constraint names and the related tables for a given schema.

First, a few words about those tables (from Re: System tables in Derby):

SYSTABLES has one row for each table in the database. Its primary key is TABLEID, which contains system-generated values. The SCHEMAID is a foreign key column, which references SYSSCHEMAS.

SYSSCHEMAS has one row for each schema in the database. Its primary key is SCHEMAID.

...

SYSCONSTRAINTS has one row for each constraint in the database (primary, unique, foreign and check constraints). Its primary key is CONSTRAINTID, which is a system-generated value. The TABLEID column is a foreign key referring to SYSTABLES.TABLEID. The SCHEMAID column is a foreign key referring to SYSSCHEMAS.SCHEMAID.

So you could use the following query:

SELECT 
    C.CONSTRAINTNAME,
    T.TABLENAME 
FROM 
    SYS.SYSCONSTRAINTS C, 
    SYS.SYSSCHEMAS S,
    SYS.SYSTABLES T 
WHERE 
    C.SCHEMAID = S.SCHEMAID 
AND 
    C.TABLEID = T.TABLEID
AND 
    S.SCHEMANAME = 'MYSCHEMA';

And then loop and build the corresponding ALTER TABLE DROP CONSTRAINT statement.

References

• Derby support for SQL-92 features
  
  • Table 22. Support for SQL-92 Features: Constraint tables
• Re: System tables in Derby

Answer 2

I'm not really sure I understand why it would be useful to drop all the constraints in your database programatically. Nonetheless, for an example of code which uses the DatabaseMetaData API to find various constraints and other dependent objects (views, etc.) for a particular schema in a Derby database, you can have a look at the dropSchema() method in the Derby testing harness's JDBC.java utility: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?view=co