Friday, February 18, 2011

Common Errors while performing DML Operations on a table.

Primary key  Constraints
Equivalent to a unique constraints, both sets of rules apply.
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

Foreign key Constraints
INSERT and UPDATE on the child table: the value must exist in the parent table.
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
UPDATE and DELETE on the parent table,
there must be no dependent rows in the child table.
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated – child record found

Not null Constraints
INSERT and UPDATE: cannot insert without a value, or modify the value to null.
ORA-01400: cannot insert NULL into (“SCOTT”.“EMP”.“EMPNO”)

Unique Constraints
INSERT and UPDATE: cannot have any value that already exists in the table. 
Multiple nulls are permitted.
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

What is ACID Property?

ACID Property - Atomicity Consistency Isolation Durability

A for Atomicity
The principle of atomicity states that all parts of a transaction must complete or not.

C for Consistency
The principle of consistency states that the results of a query must be consistent with the state of the database at the time the query started. It requires that the database ensure that changed values are  not seen by the query. Through the use of undo segments Oracle guarantees that if a query succeeds, the result will be consistent to all else data shown from undo segments.

I for Isolation
The principle of isolation states that an incomplete i.e. uncommitted transaction must be invisible to all others. While the transaction is in progress, only the one session that is executing the transaction is allowed to see the changes, all other sessions must see the unchanged data, not the new values.

D for Durability
The principle of durability states that once a transaction completes with a COMMIT, it must be impossible for the database to lose it. During the time that the transaction is in progress, the principle of isolation requires that no one can see the changes it has made so far.