Deleting foreign key references in a database table

Question

Given this problem:

Consider a relation geq which represents “greater than or equal to”, that is, (x,y)E geq only if y < x.
create table geq
( lb integer not null 
, ub integer not null
, primary key lb
, foreign key (ub) references geq on delete cascade )
Which of the following is possible if a tuple (x,y) is deleted?

(a) A tuple (z,w) with z > y is deleted
(b) A tuple (z,w) with z > x is deleted

This is how I am trying to solve it :-

Since ub (upper bound) is the foreign key, in the tuple (x,y) , y is the foreign key. Given, the foreign key (y) references on table geq itself, there must a tuple ( y , y' ) in geq.

Now, y >= x and y' >= y. Therefore, y' >= x.

So instead of using (z,w) i used (y,y').

So, shouldn't the answer be, A tuple (z,w) with w>x is deleted ?

(I am trying to solve an old GATE paper)

The y < x constraint effectively dictates a partial order (the graph is a tree or a DAG, or a collection of these) y=x is special, a self reference, in fact the smallest possible loop. — wildplasser, Dec 23 '12 at 15:07

Joel Brown · Answer 1 · 2012-12-23T15:38:55.413

If (x,y) ∈ geq if and only if x > y. Calling the relation "greater or equal to" is a misnomer, for reasons I will get into below.

You are correct that the foreign key is the upper bound (ub) meaning x is the foreign key and y is the primary key in the tuple (x,y). The on delete cascade option in the foreign key declaration means that whenever a record is deleted, any child records that refer to the deleted record will also be deleted.

For your case, if you delete tuple (x,y), any records that reference (x,y) will also be deleted.

Since the foreign key is ub, a record (z,w) is a child of (x,y) if and only if: z = y.

Therefore, for the deletion of (x,y) to cause the cascade deletion of (z,w) the following must be true:

x ≥ y = z ≥ w (This isn't quite right, as I'll explain in a moment.)

The answer to your problem is therefore that neither (a) nor (b) can be true. Also your conclusion (which isn't in the listed options) is also incorrect, w must be ≤ x, not w > x.

In fact, since the lower bound is a primary key, it is not possible for a relation (a,a) to have a child record, since that would require a violation of the primary key. Therefore the upper bound must be strictly greater than the lower bound for a record to have any child records. That means that the relationship statement above is more properly:

x ≥ y = z > w because y ≠ w

This means that when a tuple (x,y) is deleted, all you can say is that it is possible for a tuple (z,w) to be cascade deleted such that z = y or z ≤ x.

Surely it's necessary for `(a, a)` to have a child record, assuming we insert records where `lb <> ub`? As this is a self-referencing not nullable key, the first insert must be `(a, a)` referring back to itself. Subsequent inserts must either be in the form `(b, a)` or `(b, b)` — Chris Saxon, Dec 23 '12 at 16:22
@ChrisSaxon - Perhaps I should have been clearer. It is not possible for (a,a) to have a **separate** child record (a,a), because that would violate the PK. Since the question is about cascade deletes and a cascade delete won't delete the parent record twice, I didn't feel that the fact that (a,a) is self referential was important to this question. — Joel Brown, Dec 23 '12 at 17:26

score 0 · Answer 2 · answered Dec 23 '12 at 16:03

The problem is the NOT NULLable foreign key, combined with the 'y < x' constraint: it would need that for every row there would be a row with a numerically smaller id.

There are two ways to create a sentinel-value for a node without a parent:

set link-pointer to NULL
set it to the same value as id.

Code example:

CREATE TABLE geq_one
    ( id INTEGER NOT NULL PRIMARY KEY
    , parent_id INTEGER  NOT NULL REFERENCES geq_one(id) ON DELETE CASCADE
    , CONSTRAINT younger_than_parent_one CHECK (parent_id <= id)
    )
        ;
INSERT INTO geq_one(id,parent_id) VALUES
(1,1) -- sentinel
, (2,1)
, (3,1)
        ;

CREATE TABLE geq_two
    ( id INTEGER NOT NULL PRIMARY KEY
    , parent_id INTEGER  REFERENCES geq_two(id) ON DELETE CASCADE
    , CONSTRAINT younger_than_parent_two CHECK (parent_id < id)
    )
        ;
INSERT INTO geq_two(id,parent_id) VALUES
(1,NULL) -- sentinel
, (2,1)
, (3,1)
        ;

DELETE FROM geq_one WHERE id = 1;
SELECT * FROM geq_one;

DELETE FROM geq_two WHERE id = 1;
SELECT * FROM geq_two;

Deleting foreign key references in a database table

2 Answers2