Re: phantom records?

Stephen,

0) SQL> select table_name, constraint_name, constraint_type from
user_constraints where constraint_name = 'GDS_HOSTS_PK';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
GDS_HOSTS                      GDS_HOSTS_PK                   P

-->> above is just a constraint name on table gds_hosts..no issues so far..

1) SQL> select * from user_cons_columns where constraint_name =
'GDS_HOSTS_PK';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------

  POSITION
----------
ORS                            GDS_HOSTS_PK
GDS_HOSTS
GDS_HOST

--> the constraitn primary key is on ORS column and not on GDS_HOSTS it
seems..

2) you are selecting second column by the statement select gds_host from
gds_hosts;

3) and you are inserting into which columns..ORS or GDS_HOSTS..

I think there is no problem in table, constraitnt declaration and associated
index..please check your insert statement..may be column listing is the
issue...

Thanks!
Subodh
On 10 November 2010 20:00, Uzzell, Stephan <SUzzell@xxxxxxxxxx> wrote:

>  Hi listers, I’ve got an interesting one today:
>
>
>
> SQL> select table_name, constraint_name, constraint_type from
> user_constraints where constraint_name = 'GDS_HOSTS_PK';
>
>
>
> TABLE_NAME                     CONSTRAINT_NAME                C
>
> ------------------------------ ------------------------------ -
>
> GDS_HOSTS                      GDS_HOSTS_PK                   P
>
>
>
> SQL> select * from user_cons_columns where constraint_name =
> 'GDS_HOSTS_PK';
>
>
>
> OWNER                          CONSTRAINT_NAME
>
> ------------------------------ ------------------------------
>
> TABLE_NAME
>
> ------------------------------
>
> COLUMN_NAME
>
>
> -------------------------------------------------------------------------------
>
>
>
>   POSITION
>
> ----------
>
> ORS                            GDS_HOSTS_PK
>
> GDS_HOSTS
>
> GDS_HOST
>
>          1
>
>
>
> Hope that makes it clear – we have a PK on one column in that table.
>
>
>
> SQL> select gds_host from gds_hosts;
>
>
>
> GDS_HOST
>
> --------------------
>
> WB
>
> FIDELIO
>
> MF
>
> TW
>
> NETBOOKER
>
> HD
>
> DHRCOM
>
> EXPEDIA
>
> DCJONV
>
> DCAP
>
> MOBILE
>
>
>
> 11 rows selected.
>
>
>
> Pretty clear what we have in there…
>
>
>
> SQL> insert into GDS_HOSTS
>
>   2  select
>
>   3  'AA',
>
>   4  'SABRE',
>
>   5  CHANNEL_TYPE,
>
>   6  RATE_TYPE,
>
>   7  MULTI_CURRENCY_YN,
>
>   8  SESSION_CONTROL,
>
> <snip>
>
> 75  ;
>
> insert into GDS_HOSTS
>
> *
>
> ERROR at line 1:
>
> ORA-00001: unique constraint (ORS.GDS_HOSTS_PK) violated
>
>
>
> But do you see ‘AA’ as a gds_host in there?
>
>
>
> And just to prove it is not a SQL syntax issue – same insert, I just
> changed line 3 from ‘AA’ to ‘AA1’:
>
>
>
> SQL> insert into GDS_HOSTS
>
>   2  select
>
>   3  'AA1',
>
>   4  'SABRE',
>
>   5  CHANNEL_TYPE,
>
>   6  RATE_TYPE,
>
>   7  MULTI_CURRENCY_YN,
>
>   8  SESSION_CONTROL,
>
> <snip>
>
> 75  ;
>
>
>
> 1 row created.
>
>
>
> SQL> rollback;
>
>
>
> Rollback complete.
>
>
>
>
>
> I’ve dropped and recreated the index and the constraint, analyzed the
> table, but still getting the same oddness. Any suggestions?
>
>
>
> *
> _____________________________________________________________________________
> *
>
> *Stephan Uzzell |** **MICROS Systems, Inc.** *
>
>
>
> Database Administrator - OPERA Global Technical Services
>
> 7031 Columbia Gateway Dr,  Columbia, MD  21046 | ( 443.285.8000x2760 | 
> 7443.285.6505
>
>
>



-- 
==============================
DO NOT FORGET TO SMILE TODAY
==============================

Other related posts: