1

I have table MySQL with Primary Key Composed of 2 fields, as below

Script showing Primary Key

Already existing records in the table are:

Existing Records

The INSERT query I am issuing is:

The Query

When I run the query:

INSERT INTO `case_data` 
VALUES ('WCD/2016/1000017', 2, '2016-09-29', 'WCD',***********************

The error message displayed is:

[Err] 1062 - Duplicate entry 'WCD/2016/1000017' for key 'PRIMARY'

Am I violating the Primary Key constraint?

Thanks in advance.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
Kumar Kush
  • 2,495
  • 11
  • 32
  • 42
  • You can only add unique string in Primary key column. here "WCD/2016/1000017" is already extist in table, so you cannot add same string in table for primary key column. – Vivek Pipaliya Oct 08 '16 at 12:06
  • Yes, you violating the primary key contraint. – Vivek Pipaliya Oct 08 '16 at 12:10
  • @Vivek: If I were to change the value '**WCD/2016/1000017**' to something else, why would I need to use Composite Key? – Kumar Kush Oct 08 '16 at 12:37
  • You may take one more column "id" with auto increment. this may be solve your problem. – Vivek Pipaliya Oct 08 '16 at 12:45
  • Agreed. But what's wrong with manually incrementing the '**Iteration**' field? That is the very concept of Composite Key: You can keep all fields of the Key same, and change only one field of the key. – Kumar Kush Oct 08 '16 at 12:50
  • may be you can find something from here : http://stackoverflow.com/questions/12179770/mysql-1062-duplicate-entry-0-for-key-primary#answer-12179783 – Vivek Pipaliya Oct 08 '16 at 12:54

1 Answers1

1

You could check if the primary key values of a row you are trying to insert already exist in a table:

SELECT COUNT(*)
FROM case_data
WHERE caseno = 'WCD/2016/1000017' AND iteration = 2;

If it returns 0 then you will not violate the PK constraint and are safe to insert the row you wish (assuming there are no additional checks, triggers, constraints). Otherwise it will return 1 which means that you already have a row with values in those columns, thus you would violate uniqueness of the row which is not allowed.

When it returns 0 just issue an INSERT command. Also, remember to specify your column tables within the statement to make sure every value from your VALUES is being put within the right column of your destination table:

INSERT INTO case_data (caseno, iteration, casedate, casetype)
VALUES ('WCD/2016/1000017', 2, '2016-09-29', 'WCD');

Avoid using backticks around your column and table names if they don't contain alternative characters like commas or spaces. This will make your code more readable and definitely speed up your writing time.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • I tried the SELECT query you suggested, and it returns 0. But still I am not able to insert a row with the above INSERT query. – Kumar Kush Oct 08 '16 at 12:35
  • There must be something else preventing you from doing the INSERT imho. Check this: http://sqlfiddle.com/#!9/aa8ec – Kamil Gosciminski Oct 08 '16 at 12:53
  • I clicked "Build Schema" below first box and then clicked "Run SQL" below the second box. Nothing showed up in second box. Sorry, but I have never used **SQL Fiddle** website. – Kumar Kush Oct 08 '16 at 13:15
  • Well, it issued CREATE TABLE and INSERT statements. In the second box you can type `SELECT * FROM case_data` to see that everything works, and no error was there while inserting – Kamil Gosciminski Oct 08 '16 at 14:30
  • Kamil. You were right. The problem with a 3rd level trigger. Problem solved. Thankyou. – Kumar Kush Oct 09 '16 at 06:03