aspose file tools*
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
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: 598
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: 3610
    
  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: 598
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: 3610
    
  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: 598
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: 3610
    
  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: 598
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: 1701
    
  14

  • 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: 598
    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?