Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MariaDB can create duplicates?

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to setup a constraint that no product names should be the same.
When I create an unique key to the field called ProductName
where the ProductID is the primary key,
when I insert a same ProductName to the database, it overwrites the existing record.
How do I avoid that?
Thanks
Jack
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You'll have to show us the code -- ideally the table creation statement and the code used to insert the new record.

(Inserting a new record, using the INSERT statement, never overwrites an existing record - unless there is an trigger which does something behind the scenes, but you don't mention triggers in your post - so there must be something else amiss).
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:You'll have to show us the code -- ideally the table creation statement and the code used to insert the new record.

(Inserting a new record, using the INSERT statement, never overwrites an existing record - unless there is an trigger which does something behind the scenes, but you don't mention triggers in your post - so there must be something else amiss).


Nothing special really

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Two more questions:

1) How did you add the unique constraint?

2) How did you determine that the insert statement has overwritten an existing record?
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:Two more questions:

1) How did you add the unique constraint?

I click on "Create Key", and create unique key, BTW, my version is in chinese, so may not be the correct interpretation.



Martin Vajsar wrote:
2) How did you determine that the insert statement has overwritten an existing record?


I double checked on the table, and see two records with the same name were there.
unique key.png
[Thumbnail for unique key.png]
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't use that tool, but I'd expect that the unique key would be highlighted in the menu, since it should exist. Can you try to click it once more, just for sure? If there already were duplicates when you tried to create the key, the creation could have failed.

If you have duplicates, it seems the unique key doesn't exist or is disabled (if this is possible in MariaDB). In your original post, you said
Jacky Luk wrote:when I insert a same ProductName to the database, it overwrites the existing record.

That would be strange. Duplicates, on the other hand, simply indicate a non-existent or disabled unique constraint.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:I don't use that tool, but I'd expect that the unique key would be highlighted in the menu, since it should exist. Can you try to click it once more, just for sure? If there already were duplicates when you tried to create the key, the creation could have failed.

If you have duplicates, it seems the unique key doesn't exist or is disabled (if this is possible in MariaDB). In your original post, you said
Jacky Luk wrote:when I insert a same ProductName to the database, it overwrites the existing record.

That would be strange. Duplicates, on the other hand, simply indicate a non-existent or disabled unique constraint.


No, it doesn't work
duplicates.png
[Thumbnail for duplicates.png]
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
  • Your primary key appears to be the ProductID, and this is unique.
  • The Product Name is not defined as a unique key, so there is no reason for MariaDB to stop you loading a "duplicate" record.
  • Add a unique key constraint on Product Name and try again.
  •  
    Jacky Luk
    Ranch Hand
    Posts: 634
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    chris webster wrote:
  • Your primary key appears to be the ProductID, and this is unique.
  • The Product Name is not defined as a unique key, so there is no reason for MariaDB to stop you loading a "duplicate" record.
  • Add a unique key constraint on Product Name and try again.


  • Alright now, I manually typed it in, it is working now
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic