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
 

 

query on Referential integrity

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

Active User


Joined: 17 Oct 2004
Posts: 190
Location: hyderabad

PostPosted: Thu Nov 18, 2004 4:15 pm    Post subject: query on Referential integrity
Reply with quote

i have two table
one having primary key
other having foreign key who depends on primary key of 1st column


now i need to update the value of primary key of 1st table..



Thanks in advance
Siva
Back to top
View user's profile Send private message

jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Fri Nov 19, 2004 3:52 am    Post subject:
Reply with quote

You cannot Update a primary key,
you need to delete the existing row and create a new one with new key.

how ever if you have delete cascade it wil delete all the dependant rows of other table.
Back to top
View user's profile Send private message
vasanthanc

New User


Joined: 01 Apr 2005
Posts: 58

PostPosted: Wed May 11, 2005 11:56 am    Post subject:
Reply with quote

Second table cannot have the values in foreign key field which are not available in first table primary key values. In your example, second table foreign key values can be either of the values in first table primary key and not anything else. Referential integrity doesnt allow you to insert the values 30, 80 and 100 in your example. So you can insert only 10 & 40 to the second table.
To insert 30, 80 and 100 in your second table, you need to insert records in your first table with primary key values 30, 80 and 100. This is called referential integrity.
Back to top
View user's profile Send private message
brain_s390

New User


Joined: 06 May 2005
Posts: 58
Location: Mumbai

PostPosted: Sun May 15, 2005 1:36 am    Post subject: Re: query on Referential integrity
Reply with quote

I agree with what Vasanth has mentioned .

Every foreign key value must have a matching primary key value.The insertion of any given foreign key value (non-null value) or an update to that value is allowed only if the matching value exists in the primary key.A deletion of foreign key value can not violate referential integrity and no checking is required.

In your case 30, 80 and 100 shud be there as the primary key in the first table.
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Sun May 15, 2005 12:21 pm    Post subject:
Reply with quote

Hi Vasanth,

One common suggetion to all please start a new post for each question.

Coming to your question. Reference key means Primary key of one table refered as a domain to the non primary key column of another table. For example I want to create two tables to store Employee and Department information. The table structures are as follows...

EMP(EMPNO,ENAME,JOB,SAL,DEPTNO)
DEPT(DEPTNO,DNAME,LOC)

- when inserting data into employee you must refer to the DEPT table for valid department values.

If the data in DEPT is as follows..
DEPTNO DNAME LOC
10 SALES DALLAS
20 CS CHENNAI
30 IT CHICAGO

then while inserting data into EMP always you should enter only 10,20 or 30 as dept value. Because of a common business rule " If dept exists then only we can place employees"

To enforce this rule we must take DEPTNO as foreign key in EMP table which refers to Primary key DEPTNO of DEPT table.

I think now its clear to you.

Thanks,
Reddy
Back to top
View user's profile Send private message
i413678
Currently Banned

Active User


Joined: 19 Feb 2005
Posts: 112
Location: chennai

PostPosted: Tue May 17, 2005 2:19 pm    Post subject: RI
Reply with quote

Hi,

the explanation told by ovreddy is understood.

adding to that the foreign key value can be value which is matching the primary key in other table or it can be NULL.

pavan
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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