1

Hello how can i do to insert uniq rows without duplicate.

cursor.execute("CREATE TABLE IF NOT EXISTS tab1 (id varchar(36) primary key, cap1 VARCHAR(4), cap2 varchar(55), cap3 int(6), Version VARCHAR(4));")
id = uuid.uuid1()
id = str(id)
cursor.execute("INSERT IGNORE INTO tab1 (id, cap1, cap2, cap3, Version) VALUES (%s, %s, %s, %s, %s )", (vals))

caption_tab1

I should not insert the third row while is the same as first row. Hope im clear . Thank you in advance,

charles
  • 81
  • 6
  • https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ – Zeljka Mar 04 '20 at 15:03
  • @Zeljka thank you for your response. Unfortunately i saw this link and was not able to solve my problem. – charles Mar 04 '20 at 15:06

1 Answers1

0

The problem is that uuid() will always give a unique identifier and since id is a primary key, the row is getting inserted with duplicate values except for id column which is different always.

I think this link might answer your question or else, create a unique index on columns that you want to be unique.

Let me know if it helps!!

Anand Sai
  • 1,452
  • 5
  • 11
  • Thank you for you link.. have i don't know how to do in my case. Notice that the example that i have given is a sample of my data .. i cant also verify evry column and check if value exist . (cauz i have in total 90 columns in my database). Please if you have an idea don"t hesitate. – charles Mar 04 '20 at 15:13
  • Maybe i can create something like Uniq key of all fileds except id? – charles Mar 04 '20 at 15:17
  • Yeah, that's right!! As I mentioned in my answer, you can create a unique index on columns that you want to be unique. You can use this link: https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql to create a unique index. – Anand Sai Mar 04 '20 at 15:18
  • i want to leave id as primary key of the table and then create another unique key as combinasion of the other columns.? Can i do that do you think? – charles Mar 04 '20 at 15:38
  • 1
    Yeah, you can create multiple unique indexes leaving id as a primary key. You can use this link: https://www.mysqltutorial.org/mysql-unique/ to create a unique key. – Anand Sai Mar 04 '20 at 15:43
  • @charles No Problem!! – Anand Sai Mar 04 '20 at 16:42