Get Constraint and drop name next

Question

I have a constraint UNIQUE in the PERSON table and is from the field CPF. To get the name of this contraint I use the query:

SELECT UC.CONSTRAINT_NAME FROM USER_CONSTRAINTS UC
INNER JOIN USER_TAB_COLUMNS UT ON UT.TABLE_NAME = UC.TABLE_NAME
WHERE UC.TABLE_NAME = 'PESSOA' AND UC.CONSTRAINT_TYPE = 'U' AND UT.COLUMN_NAME = 'CPF';

How do a procedure where first I take this name and give the drop in it?

Answer 1

Just perform your query and make an "alter table drop Constraint", ex:

create or replace procedure DropConstraint(aTabela          varchar2,
                                           aCONSTRAINT_TYPE varchar2,
                                           aCOLUMN_NAME     varchar2) is
  cursor cConstraints is
    SELECT UC.CONSTRAINT_NAME
      FROM USER_CONSTRAINTS UC
     INNER JOIN USER_TAB_COLUMNS UT
        ON UT.TABLE_NAME = UC.TABLE_NAME
     WHERE UC.TABLE_NAME = aTabela
       AND UC.CONSTRAINT_TYPE = aCONSTRAINT_TYPE
       AND UT.COLUMN_NAME = aCOLUMN_NAME;
begin
  for c in cConstraints loop
    execute immediate ('ALTER TABLE ' || aTabela || '  DROP CONSTRAINT ' ||
                      c.CONSTRAINT_NAME);
  end loop;
end;