Foreign Key Constraint seems not to work

[GoogleCodeExporter]

What steps will reproduce the problem?
1.Create two Tables both with primary key, and one Table
  with Foreign key referencing the Other
2. view table definition with an jdbc driver 
  (you will not see Foreign key definition)
3. try to insert an invalid key will work

What is the expected output? What do you see instead?
an Exception, that foreign key constraint was violated.

What version of the product are you using? On what operating system?
sqljet.1.0.3.b914.zip

Please provide any additional information below.
Following Source Code will show the Problem (also as File attached)

import java.io.File;

import org.tmatesoft.sqljet.core.SqlJetException;
import org.tmatesoft.sqljet.core.SqlJetTransactionMode;
import org.tmatesoft.sqljet.core.table.SqlJetDb;

/**
 * @author rimkus
 *
 */
public class DatabaseTest
{   
    public static void main( String[] args ) throws SqlJetException
    {
        File dbfile = new File("data/testdb.db");
        if(dbfile.exists())
            dbfile.delete();
        SqlJetDb database = new SqlJetDb(dbfile,true);
        database.open();

        database.getOptions().setAutovacuum(true);      
        database.beginTransaction(SqlJetTransactionMode.WRITE);
        database.getOptions().setUserVersion(1);        
        database.createTable(
                                "CREATE TABLE `FILELIST` (" +
                                "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                                "`filepath` VARCHAR(300) NOT NULL" +
                                ");"
                );
        database.createTable(   
                                "CREATE TABLE `CHECKLIST` (" +
                                "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                                "`fileid` INTEGER NOT NULL," +
                                "`type` VARCHAR(50) NOT NULL," +
                                "`value` VARCHAR(500) NOT NULL," +
                                "FOREIGN KEY(fileid) REFERENCES FILELIST(id)" +
                                ");"
                );
        database.commit();

        /**
         * If you now look with an JDBC SQLite connection, 
         * the second table has no Foreign key Information
         */

        database.getTable("FILELIST").insert(1,"examplefile1.file");
        database.getTable("FILELIST").insert(2,"examplefile2.file");

        database.getTable("CHECKLIST").insert(null,1,"testtype","value_1");

        /**
         * This insert should not work, because we've
         * defined an Foreign Key for fileid  to FILELIST.id
         * the fileid '3' is never used in Column id of table FILELIST
         */
        database.getTable("CHECKLIST").insert(null,3,"testtype","value_2");

        database.close();
    }
}

Original issue reported on code.google.com by jonnyrim...@gmail.com on 20 Oct 2010 at 8:19

• Merged into: #135

Attachments:

• DatabaseTest.java

[GoogleCodeExporter]

Sorry for the Multiple Post, but there were Problems submitting my request.
please ignore the above!

Original comment by jonnyrim...@gmail.com on 20 Oct 2010 at 8:20

[GoogleCodeExporter]

Original comment by sergey.s...@gmail.com on 20 Oct 2010 at 1:11

• Changed state: Duplicate