The foreign key in Oracle XE

Oracle has introduced a small version of its DBMS that is labelled “XE”. This small version can be downloaded and used for free upon the condition that its usage is for personal study only.
I really like this application: it has a relative small footprint; it runs on a small machine and it allows you to learn Oracle in your own environment.
It is possible to play with the features that Oracle has. To refresh my knowledge on foreign keys.

Let us first start with the creation of two tables:

DROP TABLE "SCOTT"."CHILD";
CREATE TABLE "SCOTT"."CHILD" 
   (	"CHILD_ID" NUMBER NOT NULL ENABLE, 
	"PARENT_ID" NUMBER, 
	"BIJDRAGE" NUMBER, 
	 CONSTRAINT "CHILD_PK" PRIMARY KEY ("CHILD_ID")) ;

DROP TABLE "SCOTT"."PARENT";
CREATE TABLE "SCOTT"."PARENT" 
   (	"PARENT_ID" NUMBER NOT NULL ENABLE, 
	"PARENT_NAAM" VARCHAR2(20 BYTE), 
	 CONSTRAINT "PARENT_PK" 
         PRIMARY KEY ("PARENT_ID")) ;

INSERT INTO "SCOTT"."PARENT" 
(PARENT_ID, PARENT_NAAM) VALUES ('1', 'tom');
INSERT INTO "SCOTT"."PARENT" 
(PARENT_ID, PARENT_NAAM) VALUES ('2', 'ine');

INSERT INTO "SCOTT"."CHILD" 
(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('1', '1', '123');
INSERT INTO "SCOTT"."CHILD" 
(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('2', '1', '23');
INSERT INTO "SCOTT"."CHILD" 
(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('3', '2', '45');
INSERT INTO "SCOTT"."CHILD" 
(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('4', '2', '65');
INSERT INTO "SCOTT"."CHILD" 
(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('5', '2', '78');

We now have two tables: a child and a parent. With a foreign key relation, I will link the two tables. This is done with:

alter table "CHILD" 
add foreign key("PARENT_ID") 
references "PARENT"("PARENT_ID")
; 

This links the parent to the child. In this case, one record in the “parent” table may be linked to zero, one or more records in the “child” table. In it also true that a record in the child must always be linked to the parent. This can also be seen in the database diagramme:


The foreign key relation described above does not allow you to remove a parent record that has still child records. If we want to remove such a record, we should first remove the child records.
To avoid this somewhat cumbersome procedure (first delete relevant chiuld record, then remove parent record), we might use a different way of creating the foreign key:

alter table "CHILD" 
add foreign key("PARENT_ID") 
references "PARENT"("PARENT_ID")
on delete cascade
; 

In that case, removal of a parent record automatically deletes the child records. This could be quite convenient if we have a system where details are stored in a separate table, away from a general table. With the removal of records from the general table, the details are automatically removed as well.