Getting Unique Constraints while copying from 1 DB to other.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Getting Unique Constraints while copying from 1 DB to other.

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Getting Unique Constraints while copying from 1 DB to other.

    I am trying to copy data from PRO to ACP for some testing purpose....
    but am getting unique constraint even though there dosent exist any dupilcate records in PRO...
    Because both ACP & PRO have PK set of columns same...i belive i should not get this error....i mean it dosent make sense...

    PS below
    Code:
    ACP side
    
    WW15:W_DBA>copy from wwciw_dba@ww04 to wwciw_dba@ww15 insert WWCIW_SAP.ytcurr using select * from wwciw_sap.ytcurr
    FROM*********
    TO*****
    
    Array fetch/bind size is 1. (arraysize is 1)
    Will commit when done. (copycommit is 0)
    Maximum long size is 80. (long is 80)
    
    ERROR:
    ORA-00001: unique constraint (WWCIW_SAP.SYS_C001519) violated
    
    
    WW15:W_DBA>select * from dba_cons_columns where constraint_name='SYS_C001519';
    
    OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME        
    ------------------------------ ------------------------------ ------------------------------ -------
    WWCIW_SAP                      SYS_C001519                    YTCURR                         KURST              
    WWCIW_SAP                      SYS_C001519                    YTCURR                         FCURR              
    WWCIW_SAP                      SYS_C001519                    YTCURR                         TCURR              
    WWCIW_SAP                      SYS_C001519                    YTCURR                         GDATU
    Code:
    PRO Side
    
    WW04:W_DBA>select * from dba_cons_columns where constraint_name='SYS_C001519';
    
    OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME        
    ------------------------------ ------------------------------ ------------------------------ ------------
    WWCIW_SAP                      SYS_C001519                    YTCURR                         KURST              
    WWCIW_SAP                      SYS_C001519                    YTCURR                         FCURR              
    WWCIW_SAP                      SYS_C001519                    YTCURR                         TCURR              
    WWCIW_SAP                      SYS_C001519                    YTCURR                         GDATU              
    
    WW04:W_DBA> select 
      2  KURST,
      3  FCURR,
      4  TCURR,
      5  GDATU
      6  from wwciw_sap.ytcurr having count(*) > 1
      7  group by
      8  KURST,
      9  FCURR,
     10  TCURR,
     11  GDATU;
    
    no rows selected
    Can any body throw some light??

    am confused...

    Thanks
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    May 2001
    Posts
    736
    If u are using any sequences better check this from metalink

    ID:Note:125720.1

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    No sequences are not invloved here.

    Thanks anyway

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    May 2002
    Posts
    27
    try this query
    select
    KURST,FCURR,TCURR,GDATU,count(1)
    from wwciw_sap.ytcurr
    group by
    KURST,FCURR,TCURR,GDATU
    having count(1) > 1;

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I guess your WWCIW_SAP.ytcurr table in ACP side is not empty before you start copying. So some records with the same unique constraint values that you are trying to copy from the other table obviously allready exists in your destination table.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    I guess your WWCIW_SAP.ytcurr table in ACP side is not empty before you start copying. So some records with the same unique constraint values that you are trying to copy from the other table obviously allready exists in your destination table.
    Jurij :

    Before copy, we truncate the table in ACP....so no question of having any records....


    try this query
    select
    KURST,FCURR,TCURR,GDATU,count(1)
    from wwciw_sap.ytcurr
    group by
    KURST,FCURR,TCURR,GDATU
    having count(1) > 1;
    Mallika,

    Indeed by this query we get a dupilcate records....as i heard from my colleague that in PRO PK is with no validate option and can have dulicates.....

    Code:
    WW15:W_DBA> select kurst, fcurr, tcurr, gdatu, count(1) from ytcurr
      2  group by kurst, fcurr, tcurr, gdatu
      3  having count(1) > 1
      4  /
    
    KURS FCURR TCURR GDATU      COUNT(1)
    ---- ----- ----- -------- ----------
    M    GRD   USD   19990320          2
    P    USD   PTE   19960901          2
    Thnaks....but again why will the query with "having option before group by clause" will not work???

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by abhaysk
    Jurij :
    Thnaks....but again why will the query with "having option before group by clause" will not work???

    Abhay.
    Abhay,
    HAVING clause is used with GROUP BY clause and in the syntex it should be after GROUP BY.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    having is like a predicate for aggreate functions

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by SANJAY_G
    Abhay,
    HAVING clause is used with GROUP BY clause and in the syntex it should be after GROUP BY.
    Thas Correct sanjay but i think after 7i it should not be a case...
    Our PRO is 8i.

    Its irrelevent where ever u have Having clause, result shud be same...


    PS below from TEST DB (8i)

    Code:
    SQL> select * from tabl;
    
                      ID
    --------------------
                       1
                       1
                       2
                       2
                       5
                       7
    
    6 rows selected.
    
    SQL> select id,count(1) from tabl having count(1)> 1 group by id;
    
                      ID             COUNT(1)
    -------------------- --------------------
                       1                    2
                       2                    2
    
    SQL> select id,count(1) from tabl group by id having count(1)> 1;
    
                      ID             COUNT(1)
    -------------------- --------------------
                       1                    2
                       2                    2
    Thanks
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Yes I was wrong, it can be before or after GROUP BY. Just checked, it is same on 7.3.4 as well.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width