How to implement restrictions to db2 tables?

grofaty · #1 (**permalink**) 07-23-07, 09:03

Hi
I have the following problem.

Rules:
1. record from table mytable1 must exist in mytablesum
2. record from table mytable2 must exist in mytablesum
3. there should be no record in mytable1 that already exist in mytable2
4. there should be no record in mytable2 that already exist in mytable1
5. there should be no record in mytablesum that doesn't exist in mytable1 or mytable2

How check all this 5 rules inside DB2?

Ok, for rule 1 and 2 there are foreign keys. What about 3, 4 and 5? Any idea? I know I can write triggers, but solution would be quite complex, because mytable is actually from mytable1 to mytable10 and it should check data consistency after insert, update and delete.

Simple sample:

Code:

CREATE TABLE DB2ADMIN.MYTABLE1 (COL INT NOT NULL PRIMARY KEY);
CREATE TABLE DB2ADMIN.MYTABLE2 (COL INT NOT NULL PRIMARY KEY);
CREATE TABLE DB2ADMIN.MYTABLESUM (COL INT NOT NULL PRIMARY KEY);

ALTER TABLE DB2ADMIN.MYTABLE1 ADD FOREIGN KEY (COL) REFERENCES DB2ADMIN.MYTABLESUM (COL) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE DB2ADMIN.MYTABLE2 ADD FOREIGN KEY (COL) REFERENCES DB2ADMIN.MYTABLESUM (COL) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO DB2ADMIN.MYTABLESUM VALUES (1);
INSERT INTO DB2ADMIN.MYTABLE1 VALUES (1);

INSERT INTO DB2ADMIN.MYTABLESUM VALUES (2);
INSERT INTO DB2ADMIN.MYTABLE2 VALUES (2);

My system:
db2 v8 fp9 on Windows XP
Thanks,
Grofaty

n_i · #2 (**permalink**) 07-23-07, 10:34

Since you obviously did not describe your complete problem, it is difficult to make any suggestion. If you were to store your data in a single table with a unique index you would not need any constraints.

Code:

create table db2admin.asingletable(col int not null primary key, tbname varchar(8) not null);
create view db2admin.mytablesum (col) as select col from db2admin.asingletable;

insert into db2admin.asingletable (col, tbname) values (1, 'mytable1');
insert into db2admin.asingletable (col, tbname), values (2,'mytable2');

stolze · #3 (**permalink**) 07-23-07, 13:47

Quote:

Originally Posted by grofaty

1. record from table mytable1 must exist in mytablesum
2. record from table mytable2 must exist in mytablesum

Foreign keys are the answer here.

Quote:

3. there should be no record in mytable1 that already exist in mytable2
4. there should be no record in mytable2 that already exist in mytable1
5. there should be no record in mytablesum that doesn't exist in mytable1 or mytable2

Neither CHECK constrainst nor foreign keys would help you here. You can either ensure that the constraints are enforced by all your applications, or you have to resort to triggers.

(And none of the available (serious) database systems implements ASSERTIONS.)

Quote:

I know I can write triggers, but solution would be quite complex, because mytable is actually from mytable1 to mytable10 and it should check data consistency after insert, update and delete.

You could use a UNION ALL view over mytable1 ... mytable10. That would simplify the trigger definition and the DB2 optimizer can still eliminate whole tables if respective predicates allow for that.

grofaty · #4 (**permalink**) 07-24-07, 03:26

Stolce,
thanks for idea.

I have written first trigger "after delete" on mytable1:

Code:

CREATE TRIGGER DB2ADMIN.MYTABLE1_D AFTER DELETE ON DB2ADMIN.MYTABLE1 FOR EACH STATEMENT MODE DB2SQL
SELECT CASE WHEN COUNT(*)>0 THEN RAISE_ERROR('80001','Duplicate values after delete') ELSE 0 END FROM SYSIBM.SYSDUMMY1 WHERE EXISTS
(
SELECT
     COL,
     MYCOUNT
FROM (
     SELECT
          COL,
          COUNT(*) AS MYCOUNT
     FROM
          DB2ADMIN.MYTABLESUM
     GROUP BY COL
     ) AS TEMP
WHERE
     (
     COL,
     MYCOUNT
     )
     NOT IN
          (
          SELECT
               COL,
               SUM(MYCOUNT)
          FROM
          (
               SELECT COL, COUNT(*) AS MYCOUNT FROM DB2ADMIN.MYTABLE1 GROUP BY COL
               UNION ALL
               SELECT COL, COUNT(*) AS MYCOUNT FROM DB2ADMIN.MYTABLE2 GROUP BY COL
          ) AS TEMP2
          GROUP BY COL
          )
)

The same trigger logic for "after insert, update" for all mytable tables.

But there is a one problem: trigger is executed (triggered) after each delete and therefore it always return error after delete statement. I would need to trigger it at the end of transaction not after each delete.

Simple sample:
Table mytable1 has record: 1
Table mytabe2 has record: 2
Table mytabesum has: 1, 2

If I would like to delete record 1 from mytable1 (delete from db2admin.mytable where col =1) then trigger fires up and returns error, this is obvious because I also need to delete the same data from mytablesum (delete from db2admin.mytablesum where col=1). So trigger is fired up after each delete statement, but I would need some kind of functionality when trigger should be executed after transaction.

Sample1 (this functionality I would need):
First step: delete from mytable1 where col1=1
Second step: delete from mytablesum where col1=1
Third step: check consistency.

But triggers work like this. Sample2:
First step: delete from mytable1 where col1=1
Second step: check consistency.
Third step: delete from mytablesum where col1=1
Forth step: check consistency.

So second step returns error. Is there any functionality to have sample1 functionality?
I don't think such a trigger exists. So I don't think trigger is a right way of solving consistency (integrity) of data in my case.

Any idea how to solve the problem?

Thanks,
Grofaty

grofaty · #5 (**permalink**) 07-24-07, 03:43

Hi,
I have solution:
foreign keys must be created with "cascade" option. Now triggers logic works file:

Code:

ALTER TABLE DB2ADMIN.MYTABLE1 ADD FOREIGN KEY (COL) REFERENCES DB2ADMIN.MYTABLESUM (COL) ON DELETE CASCADE ON UPDATE RESTRICT

So in sample1 first delete is not needed.

So if I understand the logic DB2 works like this:
1. when delete is fired then first foreign key integrity is checked. Because foreign key integrity is defined as 'cascade' it means DB2 also deletes all rows from dependent tables
2. after foreign key consistency check DB2 fires triggers. Because no rows exist in dependent table, trigger doesn't returns error.

Thanks,
Grofaty

stolze · #6 (**permalink**) 07-24-07, 04:34

Quote:

Originally Posted by grofaty

But there is a one problem: trigger is executed (triggered) after each delete and therefore it always return error after delete statement. I would need to trigger it at the end of transaction not after each delete.

Simple sample:
Table mytable1 has record: 1
Table mytabe2 has record: 2
Table mytabesum has: 1, 2

If I would like to delete record 1 from mytable1 (delete from db2admin.mytable where col =1) then trigger fires up and returns error, this is obvious because I also need to delete the same data from mytablesum (delete from db2admin.mytablesum where col=1). So trigger is fired up after each delete statement, but I would need some kind of functionality when trigger should be executed after transaction.

Sample1 (this functionality I would need):
First step: delete from mytable1 where col1=1
Second step: delete from mytablesum where col1=1
Third step: check consistency.

But triggers work like this. Sample2:
First step: delete from mytable1 where col1=1
Second step: check consistency.
Third step: delete from mytablesum where col1=1
Forth step: check consistency.

So second step returns error. Is there any functionality to have sample1 functionality?
I don't think such a trigger exists. So I don't think trigger is a right way of solving consistency (integrity) of data in my case.

Your observation is correct. A trigger is compiled into the triggering SQL statement (DELETE in your example). So it is executed as part of that statement. DB2 does not (yet) support transaction-level triggers.

Creating the foreign keys using ON DELETE CASCADE will fix your DELETE scenario. But it will not help with INSERT/UPDATE situations.

There are surely different approaches to address your issue. One would be this:

Your applications work with mytable1 and mytable2 only when inserting/updating data. I.e. you revoke INSERT/UPDATE/DELETE privileges from mytablesum.
Triggers and foreign keys maintain the integrity of mytablesum, i.e. INSERT triggers on mytable1/mytable2 will insert the respective information on mytablesum. Likewise, UPDATE triggers handle update situations (because no ON UPDATE CASCADE clause is available for foreign keys). Delete operations can be handled with foreign keys.

Yet another idea may be to use views and INSTEAD OF triggers.

Internet Services

Web Development

Digital Marketing

Consumer Tech