OraFAQ Forum: Weblogic & Application Server » Problem with Referential Integrity

Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » Problem with Referential Integrity

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with Referential Integrity [message #166587]

Fri, 07 April 2006 02:30

Bijupm
Messages: 16
Registered: February 2006

Junior Member

In a child table, for some values in the foreign key column there is no corresponding values in the parent table.

Can anybody give a reason for this situation.

All constraints are enabled.

Report message to a moderator

Re: Problem with Referential Integrity [message #166728 is a reply to message #166587]

Fri, 07 April 2006 19:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>In a child table, for some values in the foreign key column there is no corresponding values in the parent table.
>All constraints are enabled.
Post the SQL*Plus session showing SQL statements & results that confirm both these statements are true & valid.

Report message to a moderator

Re: Problem with Referential Integrity [message #166871 is a reply to message #166728]

Mon, 10 April 2006 01:58

Bijupm
Messages: 16
Registered: February 2006

Junior Member

Posting the SQL* PLUS session.

SQL> SELECT LINELIMITATION_ID,CC_ID_CONVOYCATEGORY
FROM LINEPARTLIMITATION
WHERE CC_ID_CONVOYCATEGORY = 179766;

LINELIMITATION_ID CC_ID_CONVOYCATEGORY
----------------- --------------------
184940 179766

SQL> SELECT CC_ID,C1LENGTH
FROM CONVOYCATEGORY
WHERE CC_ID='179766';

no rows selected

Here the column CC_ID_CONVOYCATEGORY of table LINEPARTLIMITATION
is got a reference to column CC_ID of table CONVOYCATEGORY.

Not only the value 179766 , there are 69 rows like this in table
LINEPARTLIMITATION.

Report message to a moderator

Re: Problem with Referential Integrity [message #167007 is a reply to message #166587]

Mon, 10 April 2006 18:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

When things do not appear to be as the should (be), they probably are NOT as they should be!

> WHERE CC_ID='179766';
> WHERE CC_ID_CONVOYCATEGORY = 179766;

Why quote in one & not in OTHER?

Here the column CC_ID_CONVOYCATEGORY of table LINEPARTLIMITATION
is got a reference to column CC_ID of table CONVOYCATEGORY.

I do/did NOT see any reference in any posted information.

Post the results from DESC of both tables.

Report message to a moderator

Re: Problem with Referential Integrity [message #167033 is a reply to message #167007]

Mon, 10 April 2006 23:04

Bijupm
Messages: 16
Registered: February 2006

Junior Member

I am posting the session with reference and values..

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
2 FROM DBA_CONSTRAINTS
3 WHERE TABLE_NAME ='CONVOYCATEGORY' AND CONSTRAINT_TYPE = 'P';

CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
PK_CONVOYCATEGORY P CONVOYCATEGORY

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME
2 FROM DBA_CONSTRAINTS
3 WHERE TABLE_NAME ='LINEPARTLIMITATION' AND CONSTRAINT_TYPE = 'R';

CONSTRAINT_NAME C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
FK_LINEPARTLIMITATION_CC_ID R PK_CONVOYCATEGORY
FK_LINEPARTLIMITATION_LINE_ID R PK_DATALINE

SQL> select LINELIMITATION_ID,CC_ID_CONVOYCATEGORY from linepartlimitation WHERE CC_ID_CONVOYCATEGORY = 179766;

LINELIMITATION_ID CC_ID_CONVOYCATEGORY
----------------- --------------------
184940 179766

SQL> select CC_ID,C1LENGTH from convoycategory WHERE CC_ID=179766;

no rows selected

SQL>

Check it out

Report message to a moderator

Previous Topic:	how to build free sms application
Next Topic:	Changing platform and Up grading to Release 2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:16:35 CDT 2024