Re: phantom records?
- From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
- To: SUzzell@xxxxxxxxxx
- Date: Wed, 10 Nov 2010 20:53:15 +0530
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:
