CODEWITHSUNDEEP
sqloracle-databaseplsqlforeign-keys
B.R.MADHIVADHANAN
B.R.MADHIVADHANAN

Reputation: 23

Remove all constraints from a table oracle

Guys my question is I don't know the constraint names that I've added to a table. But I need to remove or disable all those foreign key constraints. But how?

SQL> desc orders;


Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL VARCHAR2(10)
 PRODUCT_ID                                         VARCHAR2(10)
 DATE_OF_ORDER                                      TIMESTAMP(6)
 CUST_ID                                            VARCHAR2(10)
 QUANTITY                                           NUMBER(38)
 TOTAL_PRICE                                        FLOAT(10)
 DELIVERY_STATUS                                    VARCHAR2(10)

Upvotes: 0

Views: 1313

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18630

If you're using a tool like sql developer you can just right click the table name in the navigator and select constraint > disable all or drop

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

The related foreign constraints might be determined by using user_constraints dictionary view, and you can disable the foreign key constraint of this table by the following code block

BEGIN
  FOR c IN
    (
      SELECT * 
        FROM user_constraints c
       WHERE c.constraint_type = 'R'
         AND c.table_name = 'ORDERS')
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '||c.table_name||
                      ' DISABLE CONSTRAINT '||c.constraint_name;
  END LOOP;
END;  
/

Upvotes: 4

Related Questions