Updating foreign key
INITIALLY IMMEDIATE Specify INITIALLY IMMEDIATE to indicate that at the start of every transaction, the default is to check this constraint at the end of every DML statement.If you do not specify INITIALLY, INITIALLY IMMEDIATE is the default. When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.
select table_name, constraint_name, DEFERRABLE, DEFERRED, VALIDATED 2 from user_constraints 3 where table_name in ('EMP', 'DEPT') 4 ORDER BY 1 ; TABLE_NAME CONSTRAINT_NAME DEFERRABLE DEFERRED VALIDATED ------------------------------ ----------------------------- DEPT PK_DEPTNO NOT DEFERRABLE IMMEDIATE VALIDATED EMP PK_EMPNO NOT DEFERRABLE IMMEDIATE VALIDATED EMP FK_DEPT NOT DEFERRABLE IMMEDIATE VALIDATED EMP SYS_C001402 NOT DEFERRABLE IMMEDIATE VALIDATED I tried: SQL set constraints pk_dept, fk_dept deferred ; set constraints pk_dept, fk_dept deferred * ERROR at line 1: ORA-02447: cannot defer a constraint that is not deferrable Check the Doc.However, it can be done by using a DEFERRABLE foreign key: select * from person; EMAIL ------------------------------ [email protected]@[email protected] so on, so on. PDF (US Ltr) - 31.6Mb PDF (A4) - 31.6Mb PDF (RPM) - 30.5Mb HTML Download (TGZ) - 7.6Mb HTML Download (Zip) - 7.6Mb HTML Download (RPM) - 6.5Mb Man Pages (TGZ) - 189.6Kb Man Pages (Zip) - 304.2Kb Info (Gzip) - 3.0Mb Info (Zip) - 3.0Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.3-7.4 My SQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent.FK_DRT555_PROCNUM_LCNCOD) violated - parent key not found Elapsed: .02 [email protected] Most people do not -- it fails the commit and I'll betcha most applications would not deal with that nicely -- they are not expecting a commit to fail.MU =================================================== that won't work until we defer the constraints: set constraints all deferred; It makes it so that any constraint that is deferrable (to be checked at COMMIT, not at the statement level) is in fact deferred. Most people would defer the constraint when needed -- especially since a primary key should never in a billion years be updated.
Search for updating foreign key:
when you wanted to verify that your update is commitable (eg: in your example, you could use set constraints to test the constraints to see if the commit will succeed or fail) October 26, 2002 - am UTC try the commands yourself. Or search for "set constraints" (with quotes) for other threads where this has been discussed. Tom: well as you know judgements keep changing as system requirements keep developing. anyway it seems to that you always should defer a foreign key constraint when you need to update parent table.