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

i have following entity bean defined 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 , setters } 

i make use of javax.persistence.entitymanager objects accessing database. i've made unit tests verify records same name different type can correctly inserted in database, , records same name , type throw exceptions, defined in code above. when make connection mysql database, works expected. however, when use same jpa entity object same unit test in sqlite, records same name , type can wrongly added. first impression sqlite not support unique constraints on multiple columns. however, read here sqlite table constraint - unique on multiple columns supported. during initialization of entitymanager, can read logs following auto-generated statement 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 happening?

sqlite supports foreign keys (with right, recommended pragma) doesn't support adding them alter table.

try set database dialect sqlite. in 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 own databaseplatform subclass , add method,

public boolean supportsforeignkeyconstraints() {     return false; } 

then use platform using "eclipselink.target-database" property.