Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

migrated from superuser.com Jun 17 '13 at 3:42

This question came from our site for computer enthusiasts and power users.

    
The only thing I can think of, is that you can link rows together through the foreign key –  parkydr Jun 16 '13 at 21:00
    
I can't see how that is possible, as the primary key column requires distinct not null values, meaning that the foreign key constraints can only point to the row itself. :) Note that it is the primary key column that is both source and destination in the foreign key constraint. –  aweis Jun 16 '13 at 21:14

2 Answers 2

up vote 1 down vote accepted

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 <schema>.act values (1, 'blah', 'another blah')";
db2 "select * from <schema>.act";
db2 "delete from <schema>.act where actno = 1";
db2 "select * from <schema>.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.

share|improve this answer

I don't know how db2 works, but I think this is making it so rows can never be deleted once added.

from Wikipeda

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table.

Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

So restrict says the row can only be deleted if the destination key does not exist, and since it is pointing at itself it will always exist, therefor it makes it so you can add and edit a row, but you can't delete it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.