A table with a foreign-key, referencing the tables own primary key

I was looking at the database sample that is provided by the DB2 server installaion. This database is the one created when running db2samle.exe from the "bin" folder. I noticed that the table "ACT" contains a primary key with a references to it self, see sql below, and was wondering if there are any logic reason for this? or is it just a glitzh in the design?

CREATE TABLE "DB2ADMIN"."ACT" (
        "ACTNO" SMALLINT NOT NULL,
        "ACTKWD" CHAR(6) NOT NULL,
        "ACTDESC" VARCHAR(20) NOT NULL
    )

ALTER TABLE "DB2ADMIN"."ACT" ADD CONSTRAINT "PK_ACT" PRIMARY KEY
    ("ACTNO");

ALTER TABLE "DB2ADMIN"."ACT" ADD CONSTRAINT "RPAA" FOREIGN KEY
    ("ACTNO")
    REFERENCES "DB2ADMIN"."ACT"
    ("ACTNO")
    ON DELETE RESTRICT;

DELETE RESTRICT in DB2 deals with when you wish to delete records from a parent table.

As noted from the DB2 Information Center:


The delete rule of a referential constraint applies only when a row of
the parent table is deleted. More precisely, the rule applies only
when a row of the parent table is the object of a delete or propagated
delete operation (defined below), and that row has dependents in the
dependent table of the referential constraint. Consider an example
where P is the parent table, D is the dependent table, and p is a
parent row that is the object of a delete or propagated delete
operation. The delete rule works as follows:


With RESTRICT or NO ACTION, an error occurs and no rows are deleted.


With CASCADE, the delete operation is propagated to the dependents of p in table D.


With SET NULL, each nullable column of the foreign key of each dependent of p in table D is set to null.


Any table that can be involved in a delete operation on P is said to
be delete-connected to P. Thus, a table is delete-connected to table P
if it is a dependent of P, or a dependent of a table to which delete
operations from P cascade.

So, while it appears like this may prevent records from being deleted from the table, it doesn't. It just prevents records from being deleted from the table if there are records in the child table. Since there is no child table, that clause is essentially worthless and really only adds cost to determining INSERT/UPDATES/DELETES. It is fanciness that serves absolutely no purpose.

You can test this yourself by issuing the following commands:

db2 "insert into .act values (1, 'blah', 'another blah')";
db2 "select * from .act";
db2 "delete from .act where actno = 1";
db2 "select * from .act";

I tested that myself against the sample database and it ran fine. You will find that it does in fact delete the record just added.

Personally I think it is just fanciness that somebody threw in there that doesn't really belong.

StackExchange Database Administrators Q#44652, answer score: 3