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?
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;