more rows popping up in prod table than the mirror table!

[dparam]

when i select validated from dba_constraints it says validated, but i didn't find a way to check whether enabled or not?. pl let me know where to check for enabled?.

One more hint is this error started from after upgrading from 8.0.4 to 8.1.7.. when we query uniqueness it says nonunique!,
is it possible for any composite primary key to be nonunique?

[PSoni]

Hi,
If date is a part of primary key then such problem may occur
if data is not inserted in proper format.

see the below example

SQL> create table t2
2 (
3 id number,
4 idate date,
5 value number);

Table created.

SQL> alter table t2 add primary key(id, idate);

Table altered.

now insert some data in table


SQL> insert into t2 values (1, sysdate, 100);

1 row created.

now again insert same data

SQL> insert into t2 values (1, sysdate, 100);

1 row created.

nmmm !!!!!!

the problem is sysdate & date type column in table . values of sysdate has been changed in next insert by some seconds
so thats why it is getting inserted.

now insert following way

SQL> insert into t2 values (1, to_char(sysdate,'dd-mon-yyyy'),100);

1 row created.

SQL> insert into t2 values (1, to_char(sysdate,'dd-mon-yyyy'),100);
insert into t2 values (1, to_char(sysdate,'dd-mon-yyyy'),100)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001873) violated

now your getting error.

So, check if there is any such reason.
hope this will help.

Thanks
P. Soni


[Edited by PSoni on 03-20-2001 at 08:00 AM]