Re: phantom records?

  • From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
  • To: SUzzell@xxxxxxxxxx
  • Date: Wed, 10 Nov 2010 15:48:54 +0100

Any before-row trigger on that table?

On Wed, Nov 10, 2010 at 3:30 PM, 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
>
>
>



-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

Other related posts: