mustafa76 بتاريخ: 16 مايو 2005 تقديم بلاغ مشاركة بتاريخ: 16 مايو 2005 When a constraint is deferred, the database will check that the constraint issatisfied only at commit time. This is useful in the case where an update toa foreign key will violate the constraint. This will allow you to change the foreign key and then cascade the changes to the parent table before committingthe change.The following example can be run from SQL*Plus:------------------------------Begin Script--------------------------------------DROP TABLE EMP;DROP TABLE DEPT;CREATE TABLE DEPT ( DEPTNO NUMBER(2) NOT NULL, DNAME CHAR(14), LOC CHAR(13), CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) NOT NULL, CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) INITIALLY DEFERRED, CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);commit;---------------------------------End Script------------------------------------- Without the deferred constraint, the following will happen when you delete "deptno number 20" from the table "dept": SQL> delete dept where deptno=20; 1 row deleted. * ERROR at line 1: ORA-02091: transaction rolled back ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated - child record foundAt this point the statement is rolled back and "dept number 20" is undeleted.When using the deferred constraint, deleting this row will not generate an error until a "commit" is performed. For example: SQL> delete dept where deptno=20; 1 row deleted. SQL> commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated - child record foundThis will then give you a chance to delete the "child" rows before issuing the"commit" command. For example: SQL> delete dept where deptno=20; 1 row deleted. SQL> delete emp where deptno=20; 5 rows deleted. SQL> commit; Commit complete.Another situation where the deferred constraint checking feature could be usefulis if you want to change the primary key value in the parent table i.e., "DEPT". SQL> update dept set deptno=25 where deptno=20; 1 row updated. SQL> update emp set deptno=25 where deptno=20; 5 rows updated. SQL> commit; Commit complete.A constraint can also be created "INITIALLY IMMEDIATE DEFERRABLE". This means that the constraint will be checked at "initially" instead of at "commit" time, unless you manually set the constraint to "deferred". For example:------------------------------Begin Script--------------------------------------DROP TABLE EMP;DROP TABLE DEPT;CREATE TABLE DEPT ( DEPTNO NUMBER(2) NOT NULL, DNAME CHAR(14), LOC CHAR(13), CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) NOT NULL, CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) INITIALLY IMMEDIATE DEFERRABLE, CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);commit;---------------------------------End Script------------------------------------- SQL> delete dept where deptno=20; delete dept where deptno=20 * ERROR at line 1: ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated - child record found SQL> set constraint emp_foreign_key deferred; Constraint set. SQL> delete dept where deptno=20; 1 row deleted.There are new columns in the "user_constraints/dba_constraints/all_constraints"views that will give you information on whether the constraint is deferrable.For example: SQL> select constraint_name,deferrable,deferred from user_constraints where constraint_name like 'EMP%'; CONSTRAINT_NAME DEFERRABLE DEFERRED ------------------------------ -------------- --------- EMP_PRIMARY_KEY NOT DEFERRABLE IMMEDIATE EMP_SELF_KEY NOT DEFERRABLE IMMEDIATE EMP_FOREIGN_KEY DEFERRABLE DEFERREDSearch Words:=============ORA-2091 ORA-2292 اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Essam بتاريخ: 16 مايو 2005 تقديم بلاغ مشاركة بتاريخ: 16 مايو 2005 Thanks... اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Essam بتاريخ: 16 مايو 2005 تقديم بلاغ مشاركة بتاريخ: 16 مايو 2005 also it can be used when there are two columns refference each other,known as"egge and checken problem" ,how I remmeber... اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
mustafa76 بتاريخ: 16 مايو 2005 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 16 مايو 2005 بالعربي يا خوي يا عصام خلي التعقيب بالعربيلا شكر على واجب انا لم افعل شيء مجرد نسخ ولصق والى الامام جميعاً اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Recommended Posts
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.