asked on

Violation of PRIMARY KEY constraint on INSERT

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

Aneesh

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!

ASKER CERTIFIED SOLUTION

morisce

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

msiedle

ASKER

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

msiedle

ASKER

Ahh, thanks morisce ...that query works :-))

Cheers,
Mark

morisce

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.

msiedle

ASKER

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

morisce

curious thing :O