Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us