Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Primary key violation

    Hi,
    I have got a very peculier kind of problem. My package is running on SQL 2000. There is a identity primary key in a table. Now when I submit the data from 2 different computer at the same time. Only one data is storing. The reason behind this is the primary key violation. as both the data are sending the request to the database at the same time.................n as the primary key is th identity column, it is storing one that value which is able to store the data at the forst hand.
    Now plz help me out in this regard.................

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Help you do what?

    Eliminate the dups, Or remove the constraint?
    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.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.
    Really....man I hate surrogates.....
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdajabarov's solution is the cleanest, but tsk, tsk,.... should'a used GUIDs...

    Gotta love those surrogate (GUID) keys!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    I'm confused, are you inserting the values into the identity column on the target table or letting target table generate the identity value?

    Blindman what's the storage size for a GUID?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    binary(16)
    "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
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:


    1. Will have to completely redesign the db, and what's most painful, - redesign the app.
    2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    table structure

    CREATE TABLE [dbo].[vfar_Bact_phylum_tb] (
    [Bact_sr_phylum] [int] IDENTITY (1, 1) NOT NULL ,
    [Bact_phylum] [varchar] (30) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[vfar_Bact_phylum_tb] WITH NOCHECK ADD
    CONSTRAINT [PK_vfar_Bact_phylum_tb] PRIMARY KEY CLUSTERED
    (
    [Bact_sr_phylum]
    ) ON [PRIMARY]
    GO

    This is my table structure. Now from two different computers i'm sending the data to be submitted to this table at the same time. But unfortunately only single data is being saved.

    All i want is to save both the data, no matter............how many simultaneous request is going to the DB.

    No, not at all............its not at all possible to change the table design at this point of time.
    Last edited by DBA_Rahul; 07-14-04 at 17:32.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Then get rid of the constraint
    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.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and of IDENTITY property.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rdjabarov
    bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:


    1. Will have to completely redesign the db, and what's most painful, - redesign the app.
    2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.
    1) Even worse, will have to redesign source apps.
    2) GUIDS are a bitch to type, but users shouldn't be entering them anyway. I think surrogate keys should be absolutely invisible to the users.

    But yeah, it's too late for this guy's purpose.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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