Search code examples
javajdbcderby

Drop all constraints in Derby via JDBC

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

Solution

  • 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