Get constraint name and drop then

1

I have a constraint UNIQUE in the PERSON table and is in the CPF field. 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 I do a procedure where first I take this name and give it the same drop?

    
asked by anonymous 13.06.2018 / 17:04

1 answer

2

Just make your query and do an "alter table drop constraint", eg:

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;
    
13.06.2018 / 17:36