JDBC SQLite does not enforce unique primary key constraint
Can anyone suggest me why my SQLite JDBC database does not enforce a unique primary key constraint?
Create table method:
public static void TableCars()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:WalkerTechCars.db");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS CARS3 " +
"(REGISTRATION TEXT PRIMRY KEY NOT NULL, " +
" PHONE TEXT NOT NULL," +
" MAKE TEXT NOT NULL, " +
" MODEL TEXT, " +
" COLOUR TEXT)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Cars3 created successfully");
}
Insert method:
public static void InsertCars(String table, String phone, String registration, String make, String model, String colour)
{
Connection c = null;
PreparedStatement pstmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:WalkerTechCars.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
String query="INSERT INTO "+table+" (PHONE,REGISTRATION,MAKE, MODEL,COLOUR) VALUES (?,?,?,?,?)";
pstmt = c.prepareStatement(query);
pstmt = c.prepareStatement
("insert into CARS3 values(?,?,?,?,?)");
pstmt.setString(1,registration);
pstmt.setString(2,phone);
pstmt.setString(3, make);
pstmt.setString(4, model);
pstmt.setString(5, colour);
pstmt.executeUpdate();
pstmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Car Records created successfully");
}
and a line of code that shouldn't be allowed but can be executed multiple times:
DbHandler.InsertCars("Cars3", "1","BN51 MZY", "Mini", "C220", "BLACK");
Your SQL state creation PRIMRY KEY
instead PRIMARY KEY
. Thus, your table was originally created with no constraint PRIMARY KEY
, and since you added an IF NOT EXISTING clause, new creations will not be activated until the table is previously deleted.
The reason your table was created without limitation PRIMARY KEY
can be found in the diagram column-constraint
:
The constraint definition is incorrect ( PRMARY
not PRIMARY
), (as marked in red), which prevents the column constraint from being sub-literate.
source to share