Databases
StackOverflow2013
Postsdbo
POHibernate UniqueConstraint multiple columns with possible null values
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate UniqueConstraint multiple columns with possible null values
    primarykey
    data
    text
    <p>I've got a problem preventing the duplicated values from being inserted into MySQL database (using Hibernate). The problem is: I have Category, which has parent Category. There can be two categories with same name, but different parents (that's why the category name cannot be used as businessId). When the category has no parent (i.e top level categories) the value of parentId is NULL. I'm unable to understand what I'm doing wrong in the following code to get the duplicated inserted in database.</p> <p>Here is my entity class.</p> <pre><code>@Entity @Table(name = "category", uniqueConstraints = {@UniqueConstraint(columnNames = {"name","parentId"})}) public class Category implements Serializable { @Id @Column(name="id") @GeneratedValue private long id; @Column(name="name") private String name; @ManyToOne(cascade={CascadeType.ALL}) @JoinColumn(name="parentId", nullable=true) private Category parent; ... } </code></pre> <p>Here is the code used to parse new categories (and I belive that something is wrong here :-|)</p> <pre><code> for (...) { Category parent = null; String [] categories = somePath.split("\\\\"); for (String cat: categories) { Category category = new Category(); category.setName(cat); category.setParent(parent); parent = categoryDB.insertCategory(category); } } </code></pre> <p>And here is the insertCategory function</p> <pre><code>public Category insertCategory (Category category) { Session session = sessionFactory.openSession(); Transaction transaction = null; try{ transaction = session.beginTransaction(); category.setId((Long) session.save(category)); transaction.commit(); } catch (HibernateException e) { if (transaction!=null) transaction.rollback(); ... } finally { session.close(); } return category; } </code></pre> <p>After executing the code I have got the following entries in database:</p> <pre><code>select * from category; +----+--------------+----------+ | id | name | parentId | +----+--------------+----------+ | 1 | A | NULL | | 4 | A | NULL | | 2 | B | 1 | | 3 | C | 2 | | 5 | B | 4 | | 6 | C | 5 | +----+--------------+----------+ </code></pre> <p>When I try to restrict only by "name", only 3 entires are inserted to database, but I cannot impose restriction by name only, because of the situation described above.</p> <p><strong>EDIT</strong>: one of the possibilities I was seeing to solve this issue was function usage in CONSTRAINT definitions (which in case of MySQL could be IFNULL(parentId,0). And it seems that such possibility is possible for example in Oracle (according to <a href="https://stackoverflow.com/a/6369748/168233">this</a> post), but not in MySQL.</p> <p><strong>EDIT 2</strong>: actually I've found in <a href="http://bugs.mysql.com/bug.php?id=8173" rel="nofollow noreferrer">MySQL bug tracker</a> people with similar problem. Since I'm using MySQL, I've checked what code is being generated while creating table and it was almost identical to the one in Bug report (which actually considered NOT bug acording to some standarts, mentioned in bugtracker. Anyways, my collegue have tried to execute equivalent code on MS SQL Server and it actually prevented him from inserting the lines with same values on name field and null parentId. Seems like there is no database level possibility (at least using MySQL) to make desired CONSTRAINT, which is quite disappointing. Accepting the answer, since it's one of possible workarounds for the issue (using <code>magic numbers</code>, which I will not stick to, prefering to keep one extra <code>find</code> query in my code)</p> <p>Any help will be apreciated, Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSerhiy
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload