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
 

 

Referential integrity b/w Parent and child tables of DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vikas kumar jain

New User


Joined: 02 Aug 2006
Posts: 9
Location: noida

PostPosted: Fri Jun 22, 2007 10:39 am    Post subject: Referential integrity b/w Parent and child tables of DB2
Reply with quote

I want to know whether to set the referential integrity b/w Parent and child tables of DB2, is it must that we need the referential table( Child table) empty(with no data)?
Back to top
View user's profile Send private message

wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Jun 22, 2007 10:58 am    Post subject: Re: Referential integrity
Reply with quote

I don't remember it exactly right now, but no, it need not be empty. Although the table will go to CHECK PENDING status and you will have to run a CHECK DATA utility.
Back to top
View user's profile Send private message
vikas kumar jain

New User


Joined: 02 Aug 2006
Posts: 9
Location: noida

PostPosted: Fri Jun 22, 2007 11:13 am    Post subject:
Reply with quote

Thanks a lot....
Cud u tell that what is basically CHECK DATA utility and what it does in brief?
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Jun 22, 2007 1:40 pm    Post subject: Re: Referential integrity b/w Parent and child tables of DB2
Reply with quote

It checks the table data for violation of constraints. Foreign key is one of the constraints. If there are no violations then CHECK PENDING status is removed.

This is DB2's way of cautioning the user about integrity of data.

Please see the 'Utility Gude' listed under link "Manual" on top of this page for more details. Hope it helps.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jun 22, 2007 2:57 pm    Post subject:
Reply with quote

A foreign key could be null, but if present should be the primary key in the parent table.

eg. An employee need not be associated with a department in EMP table but if an employee has department say 01 then there should be a row with 01 as department number in DEPT table
Back to top
View user's profile Send private message
sagard

New User


Joined: 16 May 2007
Posts: 4
Location: Pune

PostPosted: Fri Jun 22, 2007 3:09 pm    Post subject:
Reply with quote

I am not sure i understand your last quote,
'A foreign key could be null, but if present should be the primary key in the parent table.'
for what i understand is that foreign key for one table is primary key for some other table & primary key cannot be null.
Back to top
View user's profile Send private message
tosaurabh20

New User


Joined: 08 Jun 2007
Posts: 26
Location: Noida

PostPosted: Fri Jun 22, 2007 4:16 pm    Post subject:
Reply with quote

prasadvrk wrote:
A foreign key could be null, but if present should be the primary key in the parent table.

eg. An employee need not be associated with a department in EMP table but if an employee has department say 01 then there should be a row with 01 as department number in DEPT table


Hi Vikas/Sagar

This statement of Prasad is very much correct however i will make it more elaborative to make the picture very clear.

Suppose there are two tables in database named A and B. Primary key of A is foreign key of B. Now suppose if you are inserting a row in B and it is not mandatory that you need to keep a value for the foreign key column in B. It can be null. In that scenario for this row of B there wont be any relation with A. But if you are inserting the values for foreign key column in B then it is mandatory that the same value should there in primary key column of A.

Hope this clear your doubt.

Correct me if i am worng somewhere.

Thanks
Saurabh
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Jun 28, 2007 4:59 pm    Post subject:
Reply with quote

That makes more sense provided the foreign key is defined as nullable field. So i would say it is not a standard solution and you need to have a look at the catalog before deciding it.
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 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am


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