This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes MariaDB can create duplicates? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MariaDB can create duplicates?" Watch "MariaDB can create duplicates?" New topic
Author

MariaDB can create duplicates?

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
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

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Aug 02, 2012
Posts: 590
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

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Aug 02, 2012
Posts: 590
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.


[Thumbnail for unique key.png]

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Aug 02, 2012
Posts: 590
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


[Thumbnail for duplicates.png]

chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1617
    
  13

  • 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.


  • No more Blub for me, thank you, Vicar.
    Jacky Luk
    Ranch Hand

    Joined: Aug 02, 2012
    Posts: 590
    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
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: MariaDB can create duplicates?
     
    Similar Threads
    CreateFailureException
    class variable safe in struts 2 action ?
    Ejb, help
    how to display database values in a table
    Key Class for Products