Results 1 to 9 of 9

Thread: Help Checking for duplicate primary key

  1. #1
    Join Date
    Feb 2007
    Posts
    14
    Rep Power
    0

    Default Help Checking for duplicate primary key

    I want to check if a Key already exists in a Table. So the user can see a message indicating that the key is already entered in the table. How do i do that?

    Any help

    Im using Asp.Net

  2. #2
    Join Date
    Apr 2003
    Posts
    13,269
    Rep Power
    34

    Default

    Primary keys are unique. You shouldn't be needing to check for duplicate keys. If it is not unique, you need to create a another column that contains a unique value and use it as the primary key.
    "The best software is the one that fits your needs." - A_A

    Virus free since: date unknown
    Anti-virus free since: August 2008

  3. #3
    Join Date
    Jan 2004
    Posts
    2,011
    Rep Power
    0

    Default

    don't generate yoru primary key on your own. make the db do it automatically and set the field to primary key.. (auto increment).. generating it your self and maintaining its integrity would mean you'ld have to pull the entire field and check the new value against all the results and if it is already there you'ld have to regenerate and recheck untill a unique value is genreated which would make your script really slow.

  4. #4
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    death_knight i agree, dito all u said... but since not all databases support auto-incremented primary keys... we have a manual-increment table setup.
    it works a little like this:

    SequenceNumbers
    table has last incremental number for any table u want to have an (auto) incremented key.
    when u need a key u call a stored procedure which increments a field in the table, and returns that value.

    u may pass in the type of increment u want and use one procedure... or make a different procedure for each increment

    [will edit this to add SQL for tables]
    Code:
    CREATE TABLE SequenceNumber(
    	SequenceID int NOT NULL,
    	NextCellNo int NOT NULL default 0,
    	NextRowNo int NOT NULL  default 0,
    	NextColumnNo int NOT NULL  default 0,
    	CONSTRAINT PK_SequenceNumber PRIMARY KEY (SequenceID) 
    )

    Code:
    create  procedure spGetNextCellNo
    	@SequenceID	int,
    	@CellNo 	int output
    as
    
    update	SequenceNumber
    set	@CellNo = NextCellNo, CellNo = CellNo + 1
    where	SequenceID = @SequenceID
    
    return @CellNo
    Last edited by icymint3; Mar 7, 2007 at 02:13 PM.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  5. #5
    Join Date
    Feb 2007
    Posts
    14
    Rep Power
    0

    Default

    Thanks guys but the thing is im using their Id numbers as the primary key. i dont want when adding a new user to the table more that one users have the same id number. so i want to validate if its there already without going to the ugly screen saying that the primary key already exists

  6. #6
    Join Date
    Jan 2007
    Posts
    164
    Rep Power
    18

    Default

    Use a Try Catch to catch the error and display a more friendly error message...

  7. #7
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    but jjerry, the thing is, if u generate it... u will be certain that it does not already exist... without checking it... and that is faster... than checking and give error message until a key that does not exist is found.

    what database u using... maybe it has the said functionality built in already
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  8. #8
    Join Date
    Feb 2007
    Posts
    14
    Rep Power
    0

    Default

    im kinda new to ASP.net
    im using sql server 2000.
    its a good idea to let the database autogenerate the PK but due to the requirements of my project thats what i need to do.

  9. #9
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default do the check in the SP

    ok...since u insist on not using auto incr. or user prog. gen key...

    do this
    /* code starts here*/

    Create Procedure InsertRecord
    @columnKey varchar(10),
    @otherColumn varchar(20),
    @msg varchar(200) output

    AS

    set @msg = 'inserted'

    if exists(Select * from tablename where primarykeycol = @columnKey )
    begin
    set @msg = 'user already exists/ or whatever u wanna say'
    goto Handle_err
    end

    ...insert statement...

    return 0
    Handle_err:
    return -1

    GO

    /* code ends here*/


    this way it won't throw a db exeception but instead do a soft handle...u can also do other forms of logging and stuff...Also u can display the @msg variable as the user message on you frontend app.

    That should do it... u could also implement check constraints on ur table/fields but the sp will work little better for u.
    Last edited by codecarnage; Mar 9, 2007 at 11:39 AM.

Posting Permissions

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