How to CREATE TABLE with unique constraint on two columns using UCanAccess?

Question

How to write sql query to create table with unique constraint on 2 columns using jdbc: I try this code and give me "SQLException: Invalid create statement!":

 Connection conn = ConnectDB.getConnection();
    Statement stmt = null;
    try {
            String sql = "CREATE TABLE TBL_fonts" +
                    + "(char_id int not NULL, "
                    + "FW VARCHAR(255), "
                    + "code VARCHAR(255), "
                    + "character VARCHAR(255), "
                    + "CONSTRAINT fontConst UNIQUE(FW,code), "
                    + "PRIMARY KEY (char_id))";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        conn.commit();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

SQL working fine in MS-sqlserver, post your stack trace, that may help finding what's wrog — ppuskar, May 28 '14 at 07:28
Caused by: java.sql.SQLException: Invalid create statement. at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:103) at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:119) at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56) at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:160) — Ayman, May 28 '14 at 07:34
give me: UcanaccessSQLException: unexpected token: INDEX required: ( — Ayman, May 28 '14 at 07:42
Isn't the problem with your definition of the primary key instead? AFAIK that should be `CONSTRAINT PRIMARY KEY (char_id)` as well. — Mark Rotteveel, May 28 '14 at 09:18

score 3 · Answer 1 · answered Jun 01 '14 at 14:48

This issue has been fixed in UCanAccess 2.0.6.2. The code

String sql = 
        "CREATE TABLE TBL_fonts ("
        + "char_id int not NULL, "
        + "FW VARCHAR(255), "
        + "code VARCHAR(255), "
        + "character VARCHAR(255), "
        + "CONSTRAINT fontConst UNIQUE(FW,code), "
        + "PRIMARY KEY (char_id)"
        + ")";
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);

now works as expected.

How to CREATE TABLE with unique constraint on two columns using UCanAccess?

1 Answers1