I am having difficult to remove CHECK using Alter in sql. Can anyone help me please?
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)
Advertisement
Answer
Oracle does have an alter table ... drop constraint
syntax for this.
But since you created an anonymous constraint, so this is tricky – because you don’t know the name of the constraint.
One option is to use dynamic SQL to retrieve the constraint name, and drop it with an execute immediate
command:
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; /
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