Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: How do you find a primary key violation

    I need to find out which record I am violating by trying to execute this stored procedure

    Code:
    ALTER  PROCEDURE InsertCorovan2004
    AS
    INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table] 
    ([TM #],
    [FirstName],
    [LastName],
    [SS #],
    [TerminationDate],
    [Voluntary or Involuntary])
    
    SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate, VoluntaryorInvoluntary
    FROM  dbo.TERMINATION
    WHERE  (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102))
    
    GO

    when I execute it I get this error message
    Code:
    Server: Msg 2627, Level 14, State 1, Procedure InsertCorovan2004, Line 3
    Violation of PRIMARY KEY constraint 'PK_Corovan_Table'. Cannot insert duplicate key in object 'Corovan_Table'.
    The statement has been terminated.
    Stored Procedure: GamingCommissiondb.dbo.InsertCorovan2004
    	Return Code = -4
    I am trying to insert 2004 terms into the corovan table from TERMINATION table. How do you find out what records match from each table (compare the tables reocords for dups). How would I do this in the query analyzer since thats mainly what I use. I get a better understanding of how to create proceudure in the query analyzer so I;d rather stick to it.

    In short I need to find the Culprit record that is preventing me from insert the 2004 records


    Thank you
    Last edited by desireemm; 01-11-05 at 20:34.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that there are no existing rows in [GamingCommissiondb].[dbo].[Corovan_Table], then I'd use:
    Code:
    SELECT *
       FROM dbo.TERMINATION AS a
       WHERE 1 < (SELECT Count(*)
          FROM dbo.TERMINATION AS z
          WHERE  z.[TM #] = a.[TM #])
    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Will this problem ever end???????

    I cant seem to find out where the primary key violation is.
    Last edited by desireemm; 04-15-09 at 17:23.

  4. #4
    Join Date
    Jan 2005
    Posts
    19
    Will you dump the definition of both the source and destination table?

    Perhaps the primary keys are different on these, which could cause the trouble or the source doesn't an primary key and has duplicate values for what the primary key should be.
    Commenly have such problems with databases not enforcing a primary key and referencial integrity.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    both primary

    both primary keys do not allow dups..they are botht he TM#, the corovan table records every file that leaves the property and goes off site. Usually once a year files are sent off site to corovan with a bar code which also gets recorded.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I tend to believe SQL error messages.

    To find your culprit you have a couple of options.

    SELECT A.[TM #],B.[TM #]
    FROM [GamingCommissiondb].[dbo].[Corovan_Table] A
    JOIN dbo.TERMINATION B
    ON A.[TM #] = B.[TM #]
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    insert [GamingCommissiondb].[dbo].[Corovan_Table]
    ([TM #],
    [FirstName],
    [LastName],
    [SS #],
    [TerminationDate],
    [Voluntary or Involuntary])

    SELECT t.[TM #], t.FirstName, t.LastName, t.SocialSecurityNumber, t.TerminationDate, t.VoluntaryorInvoluntary
    FROM dbo.TERMINATION t
    left outer join [GamingCommissiondb].[dbo].[Corovan_Table] g
    on t.[TM #]=g.[TM #]
    WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102))
    and g.[TM #] is null

    GO
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jan 2005
    Posts
    10

    Check the data being inserted

    Well, the best way is to determine the records being inserted.

    Let say, a table named T_MEM_INFO with the following columns:
    I_SEQ_NO 9(5), (PK)
    S_NAME X(20),
    S_SS X(10),
    I_GRP_NO 9(5),
    S_PROJ_NO X(5),
    S_CTRY X(10)

    with INDEX UNIQUE (S_SS, S_PROJ_NO, I_GRP_NO).

    Now, before you insert any rows to T_MEM_INFO (esp if it would be bulk insert or through SELECT), there are conditions that you have to satisfy. These are:
    - No primary key violation
    - No duplicate on the index

    To test if there are PK violation:
    SELECT B.*
    FROM T_MEM_INFO A, T_SOURCE B
    WHERE A.I_SEQ_NO = B.I_SEQ_NO

    Resulting records of this query should not be included on the insert to T_MEM_INFO because it would violate the defined PK


    To test if there are INDEX violation:
    SELECT B.*
    FROM T_MEM_INFO A, T_SOURCE B
    WHERE A.S_SS = B.S_SS AND A.S_PROJ_NO = B.S_PROJ_NO AND A.I_GRP_NO = B.I_GRP_NO

    Resulting records of this query should not be included on the insert to T_MEM_INFO because it would violate the defined INDEX.


    THere is no other way to solve duplicate key violation than to check the records being inserted.

  9. #9
    Join Date
    Jan 2005
    Posts
    19
    Does the destination table perhaps already contain data?

    This could be a good reason why you get duplicate data ...

  10. #10
    Join Date
    Jan 2005
    Posts
    19
    Quote Originally Posted by Thrasymachus
    I tend to believe SQL error messages.

    To find your culprit you have a couple of options.

    SELECT A.[TM #],B.[TM #]
    FROM [GamingCommissiondb].[dbo].[Corovan_Table] A
    JOIN dbo.TERMINATION B
    ON A.[TM #] = B.[TM #]
    Why didn't you just say that there is probably data in
    [GamingCommissiondb].[dbo].[Corovan_Table]
    that gets inserted as result of the query:
    Code:
    SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate, VoluntaryorInvoluntary
    FROM  dbo.TERMINATION
    WHERE  (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) A
    contains data that violates the PK constraint.

    So this should give you the rows that are in trouble:
    Code:
    SELECT *
    FROM 
    WHERE [TM #] IN
    (SELECT [TM #]
    FROM  dbo.TERMINATION
    WHERE  (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102))
    )
    (btw why the converts? I would write:
    WHERE TerminationDate BETWEEN DATETIME, '31-jan-2004 00:00:00' AND '31-dec-2004 00:00:00'
    )

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by JDobbelsteen
    Does the destination table perhaps already contain data?

    This could be a good reason why you get duplicate data ...
    Yes it does..thanks so much guys for your response..I really appreciate it

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Getting an Error message

    Help I'm getting an error message with this statement...trying to find the dups.


    Code:
    SELECT *
    FROM Corovan_Table
    WHERE [TM #] IN
    SELECT [TM #]
    FROM  dbo.TERMINATION
    WHERE  (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102)) 
     
    GO

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could use:
    Code:
    SELECT *
    FROM Corovan_Table
    WHERE [TM #] IN (
    SELECT [TM #]
    FROM  dbo.TERMINATION
    WHERE  (TerminationDate BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102) AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102)) )
     
    GO
    or you could use the big hammer, like:
    Code:
    SELECT [TM #], FirstName, LastName
    ,  SocialSecurityNumber, TerminationDate, VoluntaryorInvoluntary
       FROM  dbo.TERMINATION AS t
       WHERE  (TerminationDate
          BETWEEN CONVERT(DATETIME, '2004-01-31 00:00:00', 102)
          AND CONVERT(DATETIME, '2004-12-31 00:00:00', 102))
       AND (EXISTS (SELECT *  --  Look for dupes in Corovan_Table
          FROM Corovan_Table AS z1
          WHERE  z1.[TM #] = t.[TM #])
          OR EXISTS (SELECT *  --  Look for dupes within TERMINATION
          FROM dbo.TERMINATION AS z2
          WHERE  z2.[TM #] = t.[TM #]
          GROUP BY z2.[TM #]
          HAVING 1 < Count(*))
    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Thumbs up Thank you so much

    Pat that worked perfectly. I want to thank each and everyone of you for your help...I really appreciate you guys..your the best.

    May the Lord Bless you all

    Been banging my head against the wall over this for days/weeks now. Thanks guys..good to learn something new especially when its about something you like doing

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I still want to know why you code it this way

    HAVING 1 < Count(*)

    Instead of the tradition

    HAVING Count(*) > 1

    And desiree....when are you buying?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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