sql - 如何从表中查找列的约束名称并使用单个查询将其删除

Question

我需要找到一列的约束名称，并且我需要使用单个 select 语句将其删除。如果不可能，那么替代方法是什么

我需要将此查询提供给另一个人，以便当他运行查询时，它会自动找到特定表的列的约束名称，然后将其删除.. 无需硬编码任何东西

标签： sqloracleplsqloracle11g

Answer 1

这是一个示例 - 一个测试表，NOT NULL要删除约束。

SQL> CREATE TABLE test
  2  (
  3     id    NUMBER PRIMARY KEY,
  4     name  VARCHAR2 (10) NOT NULL,            --> this constraint should be dropped
  5     sex   VARCHAR2 (1) CHECK (sex IN ('Y', 'N'))
  6  );

Table created.

SQL> SELECT table_name, column_name, constraint_name
  2    FROM user_cons_columns
  3   WHERE table_name = 'TEST';

TABLE_NAME                     COLUMN_NAM CONSTRAINT_NAME
------------------------------ ---------- ------------------------------
TEST                           NAME       SYS_C0069006     --> this one
TEST                           SEX        SYS_C0069007
TEST                           ID         SYS_C0069008

SQL>

一个函数（因为LONG数据类型 for USER_CONSTRAINTS.SEARCH_CONDITION，所以我们可以检查它是否是那个NOT NULL）：

SQL> CREATE OR REPLACE FUNCTION f_sc (par_constraint_name IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     l_search_condition  user_constraints.search_condition%TYPE;
  5  BEGIN
  6     SELECT search_condition
  7       INTO l_search_condition
  8       FROM user_constraints
  9      WHERE constraint_name = par_constraint_name;
 10
 11     RETURN l_search_condition;
 12  END;
 13  /

Function created.

SQL>

一个接受表名和列名的过程；如果有NOT NULL限制，它将被丢弃。否则，什么都不会发生：

SQL> CREATE OR REPLACE PROCEDURE p_dropcon (par_table_name   IN VARCHAR2,
  2                                         par_column_name  IN VARCHAR2)
  3  IS
  4     l_con  user_cons_columns.constraint_name%TYPE;
  5  BEGIN
  6     SELECT a.constraint_name
  7       INTO l_con
  8       FROM user_constraints a
  9            JOIN user_cons_columns b ON b.constraint_name = a.constraint_name
 10      WHERE     a.table_name = UPPER (par_table_name)
 11            AND b.column_name = UPPER (par_column_name)
 12            AND INSTR (UPPER (f_sc (a.constraint_name)), 'IS NOT NULL') > 0;
 13
 14     EXECUTE IMMEDIATE
 15           'alter table '
 16        || DBMS_ASSERT.sql_object_name (par_table_name)
 17        || ' drop constraint '
 18        || l_con;
 19  EXCEPTION
 20     WHEN NO_DATA_FOUND
 21     THEN
 22        -- There's no NOT NULL constraint on that column
 23        NULL;
 24  END;
 25  /

Procedure created.

SQL>

测试：

SQL> EXEC p_dropcon('test', 'name');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, column_name, constraint_name
  2    FROM user_cons_columns
  3   WHERE table_name = 'TEST';

TABLE_NAME                     COLUMN_NAM CONSTRAINT_NAME
------------------------------ ---------- ------------------------------
TEST                           SEX        SYS_C0069007
TEST                           ID         SYS_C0069008

好的; 列上的约束NAME已被删除。怎么样ID？

SQL> EXEC p_dropcon('test', 'id');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, column_name, constraint_name
  2    FROM user_cons_columns
  3   WHERE table_name = 'TEST';

TABLE_NAME                     COLUMN_NAM CONSTRAINT_NAME
------------------------------ ---------- ------------------------------
TEST                           SEX        SYS_C0069007
TEST                           ID         SYS_C0069008

SQL>

正如预期的那样，什么也没发生。

---

但是（虽然不太可能发生），如果您创建了自己的包含搜索字符串的检查约束IS NOT NULL，例如

SQL> CREATE TABLE test
  2  (
  3     id    NUMBER PRIMARY KEY,
  4     name  VARCHAR2 (10) NOT NULL,
  5     sex   VARCHAR2 (1) CHECK (sex IN ('IS NOT NULL'))     --> this
  6  );

Table created.

SQL>

并在该列上运行相同的过程，约束将被删除，尽管它不是您想要的：

SQL> SELECT table_name, column_name, constraint_name
  2    FROM user_cons_columns
  3   WHERE table_name = 'TEST';

TABLE_NAME                     COLUMN_NAM CONSTRAINT_NAME
------------------------------ ---------- ------------------------------
TEST                           NAME       SYS_C0069012
TEST                           SEX        SYS_C0069013
TEST                           ID         SYS_C0069014

SQL> EXEC p_dropcon('test', 'sex');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, column_name, constraint_name
  2    FROM user_cons_columns
  3   WHERE table_name = 'TEST';

TABLE_NAME                     COLUMN_NAM CONSTRAINT_NAME
------------------------------ ---------- ------------------------------
TEST                           NAME       SYS_C0069012
TEST                           ID         SYS_C0069014

SQL>

---

结论：你不应该盲目地放弃约束。