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");

      

+3


source to share


1 answer


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

:



enter image description here

The constraint definition is incorrect ( PRMARY

not PRIMARY

), (as marked in red), which prevents the column constraint from being sub-literate.

+4


source







All Articles