如何删除检查约束？

debugcn 发表于 Dev

kodder360

我很难使用sql中的Alter删除CHECK。谁能帮我吗？

CREATE TABLE MyProject_COST (
    ID int(4) NOT NULL UNIQUE,
    detail varchar2(25) NOT NULL,
    cost int(6) CONSTRAINT cost_project CHECK(cost>=500)
);

ALTER TABLE MyProject_COST ALTER COLUMN Cost int(6)

专线小巴

Oracle确实alter table ... drop constraint对此具有语法。

但是由于您创建了匿名约束，所以这很棘手-因为您不知道约束的名称。

一种选择是使用动态SQL检索约束名称，并使用以下execute immediate命令将其删除：

declare
    c_name varchar2(255 char);
begin

    select c.constraint_name into c_name
    from all_constraints c
    join all_cons_columns cc 
        on  cc.table_name = c.table_name 
        and cc.constraint_name = c.constraint_name
    where 
        cc.table_name = 'MYPROJECT_COST' 
        and cc.column_name ='COST'
        and c.constraint_type = 'C' ;

    if c_name is not null then
        execute immediate 
            'alter table myproject_cost drop constraint "' || c_name || '"';
    end if;
end;
/

DB Fiddle上的演示：

create table myproject_cost (
        id int not null unique,
        detail varchar2(25) not null,
        cost int check(cost >= 500)
    );

insert into MyProject_COST(id, detail, cost) values(1, 'foo', 0);
-- ORA-02290: check constraint (FIDDLE_XUVVCZVSYWWROHKPBFUF.SYS_C0030623) violated

declare
    c_name varchar2(255 char);
begin

    select c.constraint_name into c_name
    from all_constraints c
    join all_cons_columns cc 
        on  cc.table_name = c.table_name 
        and cc.constraint_name = c.constraint_name
    where 
        cc.table_name = 'MYPROJECT_COST' 
        and cc.column_name ='COST'
        and c.constraint_type = 'C' ;

    if c_name is not null then
        execute immediate 
            'alter table myproject_cost drop constraint "' || c_name || '"';
    end if;
end;
/
-- 1 rows affected

insert into MyProject_COST(id, detail, cost) values(1, 'foo', 0);
-- 1 rows affected

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。