|
Category :
Database
Resources -> Database Administration -> Troubleshooting & Solutions
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
One
of the most common errors encountered in oracle are errors related to
referential integrity constraint. Some of these errors include:
ORA-02266
ORA-02291
ORA-02449
ORA-28117
Proper design of integrity
constraints at the database level will go a long way in avoiding
errors related to referential integrity. Iit is important that the
tables and constraints are defined the right way and the right way
would be to define the foreign key constraints as deferrable. For
example:
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
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));
If the constraints are defined as deferrable then you can avoid
writing complicated SQL statements and avoind keeping up with the
order of inserts, updates and deletes by simply doing this:
SQL> set constraint emp_foreign_key deferred;
-- The order of deletes is now not important
SQL> delete dept where deptno=20;
SQL> delete emp where deptno=20;
-- Constraints are checked only now
SQL> commit;
-- Put the constraint back to its original state
SQL> set constraint emp_foreign_key immediate;
Another important detail when
dealing with
parent / child tables is to make sure that the FK columns on the child
tables are indexed. When you delete from the parent table, oracle runs
a query internally to check if FK values exists in the child table and
that query can
potentially have performance issues if the index does not exist on the
FK column in the child table. There will be locking issues as well due
to absence of the index on the FK column in the child table. |
