java - 通过 JDBC 删除 Derby 中的所有约束

Question

如何通过 JDBC 删除 Derby 数据库中的所有约束？

Answer 1

您可以查询系统表 SYS.SYSCONSTRAINTS、SYS.SYSTABLES 和 SYS.SYSSCHEMAS 以获取给定模式的所有约束名称和相关表。

首先，简单介绍一下这些表(来自 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.

因此您可以使用以下查询:

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';

然后循环构建相应的ALTER TABLE DROP CONSTRAINT语句。

引用资料

• Derby support for SQL-92 features
  • 表 22. 对 SQL-92 功能的支持:约束表
• Re: System tables in Derby