Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Violation of PRIMARY KEY constraint on INSERT

Posted on 2006-05-07
7
Medium Priority
?
547 Views
Last Modified: 2012-05-05
Hi all

I have some tables based on organisations (org_code) ...and I'm trying to copy data from one organisation to another via an INSERT / SELECT query ...

To get a new value for format_id (the primary key), we use another select query to get the current MAX value of the format_id field, then + 1 (So ...SELECT     RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE])

I came up with this query to copy the data but it keeps failing saying violation of primary key constraint and I can't figure out why:


INSERT INTO eHR_Accred_Communication_Format
                      (format_id, format_name, format_description, org_code, isDefault)
SELECT     (SELECT     RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE]
                       FROM          eHR_Accred_Communication_Format) AS Expr1, format_name, format_description, '000010' AS Expr2, isDefault
FROM         eHR_Accred_Communication_Format


Can anyone see where I'm going wrong?

Thanks
Mark
0
Comment
Question by:msiedle
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16625265
Hi msiedle,

I  hope that Format_Id is an identity column, in case you can follow as

INSERT INTO eHR_Accred_Communication_Format
                      ( format_name, format_description, org_code, isDefault)
SELECT      format_name, format_description, '000010' AS Expr2, isDefault
FROM         eHR_Accred_Communication_Format


If it is not the identity column
INSERT INTO eHR_Accred_Communication_Format
                      (format_id, format_name, format_description, org_code, isDefault)
SELECT     (SELECT     RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE]
                       FROM          eHR_Accred_Communication_Format) AS Expr1, format_name, format_description, '000010' AS Expr2, isDefault
FROM         eHR_Accred_Communication_Format
WHERE NOT EXISTS (SELECT 1 FROM eHR_Accred_Communication_Format WHERE format_id = (SELECT     RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE]
                       FROM          eHR_Accred_Communication_Format) )


If both of these did not help you, post the Table structure eHR_Accred_Communication_Format

Aneesh R!
0
 
LVL 5

Accepted Solution

by:
morisce earned 2000 total points
ID: 16625340
Adding the format_id of orifgin to the max should gyarantees the primary key constarint

INSERT INTO eHR_Accred_Communication_Format
                      (format_id, format_name, format_description, org_code, isDefault)
SELECT     (SELECT     RIGHT('000000' + ltrim((cast(isnull(MAX(T2.format_id), 0) AS numeric) + 1 + cast( eHR_Accred_Communication_Format.format_id AS numeric) )), 6) [NEW_CODE]
                       FROM          eHR_Accred_Communication_Format T2) AS Expr1, format_name, format_description, '000010' AS Expr2, isDefault
FROM         eHR_Accred_Communication_Format
0
 
LVL 2

Author Comment

by:msiedle
ID: 16625375
Hi Aneesh,

Unfortunately for me this particular table does not have an identity column ...we are stuck generating the varchar primary key via this query each time we INSERT (ie. SELECT     RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE]) ...which seems to be causing the problem

I tried that second query above for non identify column that you provided, but I get the same message. It doesn't appear to be taking the new format_id that I create from the (SELECT RIGHT('000000' + ...) subquery ....not sure why

The table structure of eHR_Accred_Communication_Format is as follows:

format_id varchar(6) (NOT NULL PRIMARY KEY)
format_name varchar(50)
format_description varchar(200)
org_code varchar(6)
isDefault varchar(1)

Thanks
Mark
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:msiedle
ID: 16625384
Ahh, thanks morisce ...that query works :-))

Cheers,
Mark
0
 
LVL 5

Expert Comment

by:morisce
ID: 16625428
Let me explain you why your first query doesn't work.

Assume you have 10 records with the format_id '000001', '000002', .... '000010'.
Your query will calculate firstly the max of all records before inserting the new ones. The result is '000011'.
Now the insertion based on the table of origin try to insert 10 new records with the same format_id '000011'. Logically, you hit a duplicate keys error.
0
 
LVL 2

Author Comment

by:msiedle
ID: 16625488
Ah I see now. Thank you for explaining.

One thing I've noticed now ...if I have records from format_id '000001', '000002', .... '000011', and I go to copy them with that query, it inputs the next records as '000021' and '000022', not '000012' and '000013' as expected. No drama, but I can't see why it is jumping to those format_ids instead of just going to '000011 + 1' ...strange.

      INSERT INTO eHR_Accred_Communication_Format
          (format_id, format_name, format_description, org_code, isDefault)
      SELECT(
            SELECT RIGHT('000000' + ltrim((cast(isnull(MAX(T2.format_id), 0) AS numeric) + 1 + cast( eHR_Accred_Communication_Format.format_id AS numeric) )), 6) [NEW_CODE]
          FROM eHR_Accred_Communication_Format T2
            ) AS Expr1, format_name, format_description, '000010' AS Expr2, isDefault
      FROM eHR_Accred_Communication_Format
0
 
LVL 5

Expert Comment

by:morisce
ID: 16625503
curious thing :O
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question