Re: 8.1.6: Foreign key allows violations!?!?!

Probably the FK is defined on column that can be null.

I had the same problem with a program that

• Insert some rows in a table with FK but not write ALL the columns in the FK
• then update the columns in the FK

I had a quite similar behaviour.

You can read the thread about my old message "Oracle Foreignkey: really it works?".

Hope it can help you.
And excuse me for my bad English.

"Clif Deanhardt" <oracle_at_deanhardt.com> ha scritto nel messaggio news:905F12F71forsaledeanhardtcom_at_198.99.146.10...
> 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 - 14:50:25 CST