A deeper look to constraints ...

Hi,
did it not happen to you too? You work with something a long time and because it's complex you make some suppositions which bases on experiences. But one fine day you touch really ground.
So today;-)

I am a developer DBA (what's this? A DBA which is not on production site - but on the site where the software is developed to support/convince the developers and to do the database side stuff).

Even though the Software works with an object model we let the database be the master of data. We have an Activity table which holds the activities of a company. The activities are linked together in a Double Linked List (previous/next Activity) implemented with foreign key
relationships.

I made following notice :

When I deleted all Activities of a company I got no
FOREIGN KEY Violation?

SQL>
delete from activity where company_id
='5F765BAACB194E56A43EBF35D5A6694A';
11
rows
deleted

Let us look closer :

When Oracle starts to delete one of these four activities a FOREING KEY is always violated.

Activity (1) Activity (2) Activity (3) Activity (4)
ID <- PREV_ID NEXT_ID -> ID (NULL)
NEXT_ID -> ID <- PREV_ID <- PREV_ID NULL <- PREV_ID NEXT_ID -> ID NEXT_ID -> NULL

REMARK from author : did not succeed inlcuding a HTML-Table - sorry :-(

As following SQL shows:

SQL>
delete from activity where company_id
='5F765BAACB194E56A43EBF35D5A6694A'
and rownum < 3;
delete
from activity where company_id ='5F765BAACB194E56A43EBF35D5A6694A'
and rownum < 3
ORA-02292:
Integritäts-Constraint (CRE.ACTIVITY_NEXT_FK) verletzt -
untergeordneter Datensatz gefunden

I could not bring these experiences together :

1. Delete of one linked Activity lead to FOREIGN KEY VIOLATION
2. Delete of the complete List -> NO FOREIGN KEY VIOLATION

Contacted Oracle support. - 30 minutes later

the oracle support analyst gave me the hint from Oracle Concepts
Guide:

This case also shows that constraint checking is deferred until the
complete execution of the statement. All rows are inserted first, then all rows are
checked for constraint violations. You can also defer the checking of constraints until the
end of the transaction.

Now I understood:

• First Oracle deletes the complete rowset and then it checks the Referential integrity.

• This means even in a DML operation a Constraint can be violated and no error is raised at the end of DML when the constraint then is not violated any more.

• Deleting the complete linked List ends normally – All activities have been deleted so no one exists to point to a deleted one.

So I learned this today and I am happy to be able to learn again interesting aspects or oracle and come into the deep! On development side we only implement a FOREIGN KEY ON DELETTE CASCADE Relationship referencing the Company table.

Carl

You can test this – here the scripts and a data generator.

The Table DDL :

-- Create table
create table
ACTIVITY
(
ID RAW(16) not null,

COMPANY_ID RAW(16) not null,
NEXT_ID RAW(16),

PREV_ID RAW(16)
)
;

-- Create/Recreate primary, unique and foreign key constraints
alter table ACTIVITY
add constraint ACTIVITY_PK
primary key (ID)
;
alter table ACTIVITY
add constraint
ACTIVITY_NEXT_FK foreign key (NEXT_ID)
references ACTIVITY
(ID);
alter table ACTIVITY
add constraint ACTIVITY_PREV_FK
foreign key (PREV_ID)
references ACTIVITY (ID);
--
Create/Recreate indexes
create index ACTIVITY_NEXT_FK on ACTIVITY
(NEXT_ID)
;
create index ACTIVITY_PREV_FK on ACTIVITY
(PREV_ID)
;


The generator Test-Script :
DECLARE

l_activity PLS_INTEGER :=0;
l_Company_id
RAW(16);
l_prev_act_id RAW(16);
l_Curr_act_id RAW(16);
BEGIN

FOR Company IN 1 .. 500
LOOP

l_Company_id := SYS_GUID();
l_prev_act_id :=
SYS_GUID();

INSERT INTO
Activity
(Id,
Company_Id,
Next_Id,
Prev_Id)
VALUES
(l_prev_act_id,
l_Company_Id,
NULL,
NULL);


FOR
ii IN 1 .. 10 LOOP

l_Curr_act_id := SYS_GUID();

INSERT
INTO
Activity
(Id,
Company_Id,
Next_Id,
Prev_Id)
VALUES
(l_Curr_act_id,
l_Company_Id,
NULL,
l_prev_act_id);

UPDATE
Activity Act
SET Act.Next_Id = l_Curr_act_id
WHERE Id =
l_prev_act_id;

l_prev_act_id := l_Curr_act_id;

END
LOOP;
END LOOP;
END;
/