8.1.6: Foreign key allows violations!?!?!

I have 2 very strange, but related, situations.

I've got two tables:

   Table A

      46 million rows
      primary key: COL_A
      foreign key COL_B references B ( COL_B );
      COL_B is "not null"
      there are many other columns

   Table B
      500 rows
      primary key: COL_B
      there are many other columns

I execute this query:

   select count(*)

      from TABLE_A, TABLE_B
      where TABLE_A.COL_B = TABLE_B.COL_B(+)
        and TABLE_A.rowid in ( <two rows I picked out at random> ) ;

and I get a count of two.

I turn "count(*)" into "TABLE_A.*" like this:

   select table_a.*

      from TABLE_A, TABLE_B
      where TABLE_A.COL_B = TABLE_B.COL_B(+)
        and TABLE_A.rowid in ( <two rows I picked out at random> ) ;

and I get only ONE ROW. That's strange situation number 1.

Here's number 2....
The COL_B that's in the two TABLE_A records I selected DOESN'T EXIST IN TABLE_B. The foreign key is there, and a query of DBA_CONSTRAINTS says that it's enabled, not deferrable, and Validated. I rebuilt the TABLE_B primary key index, disabled the TABLE_A foreign key, and reenabled it with ENABLE VALIDATE. The problem still exists.

I tried recreating the problem in test tables, but the FK seems to work as I'd expect there.

I ran DBV on all the datafiles in the instance and it came back clean.

I'm running Oracle 8.1.6 on Solaris 2.6.

Any advice would be greatly appreciated.

Clif Received on Fri Mar 09 2001 - 00:36:31 CST