To Remove All Constraints

Last night, while I was coding a new program, I realized that I created wrong constraints. Then I found two methods to fix my mistake.

1- Drop table and create the table again. I think, this is not a desired solution.

DROP TABLE table_name CASCADE CONSTRAINTS;

2- If you have lots of constraints, you cannot write one by one this line.. constraint_names are also so long 🙂

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

So, you can use this procedure;

CREATE OR REPLACE PROCEDURE disable_fk_constraint IS

CURSOR fke_cur IS
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_name LIKE ‘<<some criteria to specify your constraints>>’;

ExStr VARCHAR2(4000);

BEGIN
  FOR fke_rec IN fke_cur
  LOOP
    ExStr := ‘ALTER TABLE ‘ || fke_rec.table_name ||
             ‘ DROP CONSTRAINT ‘ ||
              fke_rec.constraint_name;
    BEGIN
      EXECUTE IMMEDIATE ExStr;
    EXCEPTION
      WHEN OTHERS THEN
                dbms_output.put_line(‘Dynamic SQL Failure: ‘ || SQLERRM);
               dbms_output.put_line(‘On statement: ‘ || ExStr);    END;
  END LOOP;
END disable_fk_constraint;
/

You can also use this query for the procedure;

SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_name NOT LIKE ‘SYS%’ AND table_name = “table_name“;

There are some constraints about your table that are starts with SYS_CXXXXXX. They are constraints like NOT NULL, that are you specified in table. Ex :

CREATE TABLE ht_projects (
   project_id              INTEGER           NOT NULL,
   project_name            VARCHAR2(100)     NOT NULL,
   start_date              DATE              NOT NULL,
   target_end_date         DATE              NOT NULL,
   actual_end_date         DATE
);

If you want you can also drop them by appling the above procedure. Only change the query.

You can see all your constraints with this query :

SELECT table_name, constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME = “table_name“;

You can examine my example.

 Example

Good coding;