Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Adding a new table in heirarchy

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
morfius9
Warnings : 1

New User


Joined: 22 Feb 2006
Posts: 27

PostPosted: Mon Dec 04, 2006 1:36 pm    Post subject: Adding a new table in heirarchy
Reply with quote

I have a number of DB2 tables. There is a table A which has a cascade delete relationship with table B. Is it possible to add a new table C with a cascade delete relation set from B.
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Mon Dec 04, 2006 9:40 pm    Post subject: Re: Adding a new table in heirarchy
Reply with quote

It is possible, here are details from manual, read para in bold:


ON DELETE
Specifies what action is to take place on the dependent tables when a row of the parent table is deleted. There are four possible actions:
NO ACTION (default)
RESTRICT
CASCADE
SET NULL
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.

If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.
SET NULL must not be specified unless some column of the foreign key allows null values. Omission of the clause is an implicit specification of ON DELETE NO ACTION.

1 If T1 is delete-connected to T2 through multiple paths, 1 defining two SET NULL rules with overlapping foreign key definitions 1 is not allowed. 1 For example: T1 (i1, i2, i3). 1 Rule1 with foreign key (i1, i2) and Rule2 with foreign key (i2, i3) 1 is not allowed.

1 The firing order of the rules is: 1
1 1 RESTRICT 1 SET NULL OR CASCADE 1 NO ACTION
1 If any row in T1 is affected by two different rules, an 1 error occurs and no rows are deleted.

3 A referential constraint cannot be defined if it would 3 cause a table to be delete-connected to itself by a cycle involving 3 two or more tables, and where one of the delete rules is RESTRICT or 3 SET NULL (SQLSTATE 42915).

3 A referential constraint that would cause a table to be 3 delete-connected to either itself or another table by multiple paths 3 can be defined, except in the following cases (SQLSTATE 42915):

3 3 A table must not be both a dependent table in a CASCADE 3 relationship (self-referencing, or referencing another table), and 3 have a self-referencing relationship in which the delete rule is 3 RESTRICT or SET NULL. 3 A key overlaps another key when at least one column in one key 3 is the same as a column in the other key. 3 When a table is delete-connected to another table through multiple 3 relationships with overlapping foreign keys, those relationships 3 must have the same delete rule, and none of the delete rules can be 3 SET NULL. 3 When a table is delete-connected to another table through 3 multiple relationships, and at least one of those relationships is 3 specified with a delete rule of SET NULL, the foreign key 3 definitions of these relationships must not contain any partitioning 3 key or MDC key column. 3 When two tables are delete-connected to the same table through 3 CASCADE relationships, the two tables must not be delete-connected 3 to each other if the delete rule of the last relationship in each 3 delete-connected path is RESTRICT or SET NULL.
3 If any row in T1 is affected by different delete rules, 3 the result would be the effect of all the actions specified by these 3 rules. 3 AFTER triggers and CHECK constraints on T1 will also see the effect 3 of all the actions. 3 An example of this is a row that is targeted to be set null through 3 one delete-connected path to an ancestor table, and targeted to be 3 deleted by a second delete-connected path to the same ancestor 3 table. 3 The result would be the deletion of the row. 3 AFTER DELETE triggers on this descendant table would be activated, 3 but AFTER UPDATE triggers would not.

In applying the above rules to referential constraints, in which either the parent table or the dependent table is a member of a typed table hierarchy, all the referential constraints that apply to any table in the respective hierarchies are taken into consideration.

Manual link:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007870.htm
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us