Violation of PRIMARY KEY problem 
Author Message
 Violation of PRIMARY KEY problem

Hi all.

SQL 2k Dev ed
Win 2k Pro

I have a "Link table" to store link IDs between two other tables

LinkTable
        ID1 INT
        ID2 INT

Primary Key = ID1, ID2

I call a stored Proc  (from ASP)

        MyProc (title, description, links)

it adds a new row with the title & description and gets the new ID
back for row.


then I add links into the link table, the links are passed into the
stored proc as a string with numbers seperated by commas

eg      links = "1,2,3,4,5,3,6,7'

the function dbo.fn_intlist_to_table() returns the links list as a
table with 8 rows 1 column.

What I want to add into the Link table is the following values.
        NewID, 1
        NewID, 2
        NewID, 3
        NewID, 4
        NewID, 5
        NewID, 3    <<<< Except for this one!!!
        NewID, 6
        NewID, 7

but as you can see the number "3" is in the links list TWICE... which
means when it adds it to the LinkTable it fails with the following
error:

        Violation of PRIMARY KEY constraint 'PK_LinkTable '. Cannot
insert duplicate key in object 'LinkTable'.

This is the Code I'm using to put the values into the LinkTable

INSERT INTO LinkTable (ID1, ID2)



How can I do the same thing but if the row already exists in the
LinkTable just ignore the current link and move onto the next one.

Thanks for any help on this.

Alan



Tue, 20 Jun 2006 22:46:33 GMT
 Violation of PRIMARY KEY problem

Quote:
> but as you can see the number "3" is in the links list TWICE...

How is this links list built?  Couldn't you prevent a single value from
appearing twice?

Or, instead of treating each one separately, use an IN clause... then it
doesn't matter how many times a single value is listed.



Tue, 20 Jun 2006 22:49:42 GMT
 Violation of PRIMARY KEY problem


Quote:
> Hi all.

> SQL 2k Dev ed
> Win 2k Pro

How about just supressing duplicates returned from dbo.fn_intlist_to_table?

 INSERT INTO LinkTable (ID1, ID2)



David



Tue, 20 Jun 2006 22:57:35 GMT
 Violation of PRIMARY KEY problem

Hi

The links list is taken from several drop down boxes on the HTML front
end that lists the options. but the user could select the same link in
2 seperate boxes (which he shouldnt do)

IN clause? how would I use that in this situation?

Thanks for the help.



Quote:
>> but as you can see the number "3" is in the links list TWICE...

>How is this links list built?  Couldn't you prevent a single value from
>appearing twice?

>Or, instead of treating each one separately, use an IN clause... then it
>doesn't matter how many times a single value is listed.



Tue, 20 Jun 2006 23:11:33 GMT
 Violation of PRIMARY KEY problem

Thanks David :) that worked a treat.

How we forget about the little things in life :)

Thanks.

Al.

On Fri, 2 Jan 2004 15:57:35 -0600, "David Browne" <davidbaxterbrowne

Quote:

>DISTINCT



Tue, 20 Jun 2006 23:18:06 GMT
 Violation of PRIMARY KEY problem

Quote:
> The links list is taken from several drop down boxes on the HTML front
> end that lists the options. but the user could select the same link in
> 2 seperate boxes (which he shouldnt do)

You can use JavaScript to fiddle with the list, or prevent multiple
selection, before the list ever got to the server.  You could also filter
the list down before you send it to the database.

Quote:
> IN clause? how would I use that in this situation?

Well, assuming these id numbers come from another table, you could say


    FROM othertable WHERE number IN (1,2,3,5,3)



Tue, 20 Jun 2006 23:19:06 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

2. Primary Key Contraint violation problem

3. Violation of Primary Key constraint

4. Violation of PRIMARY KEY constraint

5. Primary key Violation during Transaction replication even tho record DOES NOT exist

6. Violation of primary key constraint

7. ODBC Violation of Primary Key Constraint Can't Update Reord

8. Primary Key Violation - Why???

9. Violation of PRIMARY KEY constraint

10. IDENTITY (MSSQL 6.5) - Primary Key violation

11. Trapping Insert Error (i.e. Primary Key Violations)


 
Powered by phpBB® Forum Software