query on Referential integrity

sivatechdrive · Posted: Thu Nov 18, 2004 4:15 pm

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

jz1b0c · Posted: Fri Nov 19, 2004 3:52 am

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.

vasanthanc · New User Joined: 01 Apr 2005 Posts: 58

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.

brain_s390 · New User Joined: 06 May 2005 Posts: 58 Location: Mumbai

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.

ovreddy · Posted: Sun May 15, 2005 12:21 pm

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

i413678 · Posted: Tue May 17, 2005 2:19 pm

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