Why JPA declaration of unique multiple constrains works in mySQL but not in SQLite

Question

I have the following entity bean defined with JPA

    @Entity
    @Table(name = "Person", schema = "", uniqueConstraints = {
        @UniqueConstraint(columnNames = {"Name", "Type"}),
        @UniqueConstraint(columnNames = {"PersonID"})})
    public class PersonDataBean implements Serializable {
    private static final long serialVersionUID = 1L;
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Basic(optional = false)
        @Column(name = "PersonID", nullable = false)
        private Integer personID;
        @Column(name = "Name", length = 255, nullable = false)
        private String name;
        @Column(name = "Type", length = 255, nullable = false)
        private String type;

       //getters and setters
}

I make use of the javax.persistence.EntityManager objects for accessing the database. I've made some unit tests to verify that records with the same name but different type can be correctly inserted in the database, and records with the same name AND type throw exceptions, as it is defined in the code above. When I make a connection to a mySQL database, everything works as expected. However, when I use the same JPA entity object with the same unit test in SQLite, then records with the same name and type can be wrongly added. My first impression was that SQLite does not support unique constraints on multiple columns. However, I read here SQLite table constraint - unique on multiple columns that this is supported. During the initialization of the EntityManager, I can read from the logs the following auto-generated statement for the table creation

CREATE TABLE Person (PersonID INTEGER NOT NULL, Name VARCHAR(255) NOT NULL, Type VARCHAR(255) NOT NULL PRIMARY KEY (PersonID))
ALTER TABLE Person ADD CONSTRAINT UNQ_Person_0 UNIQUE (Name, Type)

Any ideas why this is happening?

What JPA implementation do you use? Did you set the sqlite dialect in it? — Stefaan Neyts, Mar 31 '15 at 20:21
I'm using EclipseLink (JPA 2.0) [version 2.2.0] in my pom. How can I set specific database dialect? — pkran, Mar 31 '15 at 20:45
[H2 embedded database](http://www.h2database.com/html/main.html) as an alternative to sqlite. Been very good to me recently. — Shane Voisard, Mar 31 '15 at 21:28

Stefaan Neyts · Answer 1 · 2015-03-31T21:05:43.303

0

SQLite supports foreign keys (with the right, recommended pragma) but doesn't support adding them with ALTER TABLE.

Try to set the database dialect to sqlite. In your persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence">
    <persistence-unit name ="em1">
        <jta-data-source>jdbc/MyDB</jta-data-source>
        <properties>
            <property name="eclipselink.target-database" 
                value="sqlite"/>
        </properties>
    </persistence-unit>
</persistence>

I doubt eclipse link knows sqlite. Otherwise add your own DatabasePlatform subclass and add the method,

public boolean supportsForeignKeyConstraints() {
    return false;
}

Then use this platform using the "eclipselink.target-database" property.

edited Mar 31 '15 at 21:05

answered Mar 31 '15 at 20:57

Stefaan Neyts

2,054
1
16
25

Yeap, 'sqlite' is not listed here http://eclipse.org/eclipselink/documentation/2.5/jpa/extensions/p_target_database.htm and it throws me a runtime ClassNotFoundException: Description: Database platform class [sqlite] not found – pkran Mar 31 '15 at 21:13
My suggestion would be to use a Java embedded database, like H2, HSQL or Derby. – Stefaan Neyts Mar 31 '15 at 21:29

Why JPA declaration of unique multiple constrains works in mySQL but not in SQLite

1 Answers1