Akdora’s Blog

Programming, Oracle, Life, Fun

To Remove All Constraints January 12, 2007

Filed under: Oracle — Akdora @ 10:34 am
Tags: , , , , ,

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;

 

6 Responses to “To Remove All Constraints”

  1. ahmad Says:

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

  2. dizi izle Says:

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

  3. dizi Says:

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

  4. sandrar Says:

    Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

  5. Kaya T. Says:

    Super site. Thx!🙂

  6. Alison Says:

    Thank you. Just what I was looking for. Saved me a lot of effort.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s