oracle-databasekeydatabase-integrity

"SET FOREIGN_KEY_CHECKS = 0;" Oracle Equivalent

Is there some equivalent to the Mysql specific instruction that disable the check of the foreign keys constraints ?
SET FOREIGN_KEY_CHECKS = 0;

Solution

  • There is no command in Oracle that will disable all constraints at once.

    However it seems you want to disable constraints in the context of dropping tables. In that case you can use the CASCADE CONSTRAINTS clause to drop the referencing constraints from other tables along with the table being dropped.

    Here's an example:

    SQL> CREATE TABLE t1 (ID NUMBER PRIMARY KEY);

Table created

SQL> CREATE TABLE t2 (ID NUMBER REFERENCES t1);

Table created

SQL> INSERT INTO t1 VALUES (1);

1 row inserted

SQL> INSERT INTO t2 VALUES (1);

1 row inserted

SQL> -- this fails because of the foreign key
SQL> DROP TABLE t1;

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> DROP TABLE t1 CASCADE CONSTRAINTS;

Table dropped