can i make exisitng Foreign key constraint deferrable?
417430May 11 2009 — edited May 11 2009Hello, Is it possible to make the existing FK constraint deferrable? We can do this when we add New FK constraint. But i am not sure, we can make deferrable???
scott@orcl> CREATE TABLE MASTER(id NUMBER(10));
Table created.
scott@orcl> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));
Table created.
scott@orcl>
scott@orcl> ALTER TABLE master ADD PRIMARY KEY (id);
Table altered.
scott@orcl>
scott@orcl> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master(id)
4 DEFERRABLE
5 /
Table altered.
scott@orcl>
scott@orcl> drop table child;
Table dropped.
scott@orcl> drop table master;
Table dropped.
scott@orcl>
scott@orcl> CREATE TABLE MASTER(id NUMBER(10));
Table created.
scott@orcl> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));
Table created.
scott@orcl>
scott@orcl> ALTER TABLE master ADD PRIMARY KEY (id);
Table altered.
scott@orcl>
scott@orcl> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master(id)
4 /
Table altered.
scott@orcl> alter table child modify constraint
2 fk_master deferrable;
fk_master deferrable
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
scott@orcl>
How can we make existing FK constraint as deferrable? Any help is appreciated....