"SET FOREIGN_KEY_CHECKS = 0;" Equivalent to Oracle

Question

Is there any equivalent to a special Mysql statement that disables the checking of foreign key constraints?
SET FOREIGN_KEY_CHECKS = 0; >

Answer 1

Oracle does not have a command that immediately disables all restrictions.

However, it looks like you want to disable restrictions in the context of deleting tables. In this case, you can use the CASCADE CONSTRAINTS to remove link bindings from other tables along with the drop table.

Here is 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 

Answer 2

SET FOREIGN_KEY_CHECKS = 0; runs on a session basis . In the context of Oracle, I can only imagine that you need to do this when you have round links.

You commented that this is what you want to do:

 SET FOREIGN_KEY_CHECKS = 0; DROP TABLE table1; DROP TABLE table2; SET FOREIGN_KEY_CHECKS = 1; 

I assume that this means that TABLE1 has a foreign key referencing TABLE2, and TABLE2 has a foreign key referencing TABLE1.

If so, then Moudiz's answer is correct. You want to disable foreign keys before deleting the table:

 alter table table1 disable constraint <constraint_name>; alter table table2 disable constraint <constraint_name>; drop table table1; drop table table2; 

It makes no sense to disable all foreign keys for the duration of the session, you are only interested in two of them, both of which will be deleted using the table.

You do not want to disable all foreign keys.

The only other context I can think of is if you want to insert something into your circular link, in which case you will declare the constraint as DEFERRABLE. This means that the constraint check is performed at the end of the transaction, and not during DML, see the documentation .

If your links are not circular, just drop the tables in a different order.

Answer 3

IF you request a request to disable a foreign key, try the following:

ALTER TABLE mytable
disable CONSTRAINT fk_mytable;