Forums Register Login

MariaDB can create duplicates?

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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).
+Pie Number of slices to send: Send
 

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

+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
 

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]
1
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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]
1
+Pie Number of slices to send: Send
  • 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.
  • +Pie Number of slices to send: Send
     

    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
    "How many licks ..." - I think all of this dog's research starts with these words. Tasty tiny ad:
    a bit of art, as a gift, the permaculture playing cards
    https://gardener-gift.com


    reply
    reply
    This thread has been viewed 958 times.
    Similar Threads
    CreateFailureException
    Ejb, help
    Key Class for Products
    class variable safe in struts 2 action ?
    how to display database values in a table
    More...

    All times above are in ranch (not your local) time.
    The current ranch time is
    Mar 30, 2024 02:03:59.