SQL Server Performance

Violation of Primary Key...

Discussion in 'General Developer Questions' started by ramkumar.mu, Sep 1, 2006.

  1. ramkumar.mu New Member

    I have a newly created table with no records in it. The structure of the table is Store_Ref_Novarchar
    Section_Ref_Idint
    Store_Section_Idvarchar
    Section_Descvarchar
    Registered_group_idchar
    Date_Createddatetime
    Created_Byvarchar
    Statusvarchar

    There is a primary key defined on
    Store_Ref_Novarchar
    Section_Ref_Idint
    Store_Section_Idvarchar
    Section_Descvarchar

    I tried inserting this table from another table (#temp) with similar table structure with no primary keys and no constraints. It throwed an error "Violation of Primary key... cannot insert duplicate value into the table..."

    What could be the problem???

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. FrankKalis Moderator

    Looks like you have duplicates in your temp table.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. ramkumar.mu New Member

    No Frank. Data in my temp table is unique. any other clues???

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. ramkumar.mu New Member

    Additional info... I got this error in my development server. but when i tested that with sample data in my local machine, it ran fine.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  5. ramkumar.mu New Member

    quote:Originally posted by ramkumar.mu

    No Frank. Data in my temp table is unique. any other clues???
    Also while inserting i had a "where not exists" check condition to remove the duplicates

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  6. Adriaan New Member

    WHERE NOT EXISTS evaluates the rows before the insertion. It does not double-check during the insertion.

    So you still have to eliminate duplicates from the source data.
  7. dineshasanka Moderator

    remove the primary key constraint and insert data to the table. if that is success (Which should be), then you have duplicate data in temp table.

    another way, set the same primary key for the temp table too.


    ----------------------------------------

  8. ramkumar.mu New Member

    "Where not exists" which i gave was just for standards. I already ensured that temp table had no duplicates with the below mentioned query...

    SELECT Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc
    from #temp
    group by Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc
    having count(*) > 1

    The above query returned no results which brought me to a conclusion that the #temp table doesnt have any duplicates. correct me if i am wrong here...


    quote:Originally posted by Adriaan

    WHERE NOT EXISTS evaluates the rows before the insertion. It does not double-check during the insertion.

    So you still have to eliminate duplicates from the source data.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  9. Adriaan New Member

    You have both Store_Section_Id and Section_Desc - are you sure the Section_Desc column is not redundant?

    I would also compare the number of rows returned by ...

    (1)
    SELECT Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc from #temp

    (2)
    SELECT DISTINCT Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc from #temp

    If Section_Desc is indeed redundant, then run those two queries without Section_Desc.

    Does your #temp table have a PK or a unique constraint on these 4 columns? Or is it on 5 columns, where the additional column is an IDENTITY column?
  10. ramkumar.mu New Member

    When i compared the rows from 1 and 2, the count was same.

    My #temp table doesnt have any contraints. Is this a problem???

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page