SQLite, Error NOT NULL constraint failed on Primary Key column-CodePudding

I have problem while using expo-sqlite. Whenever I tried to insert values to table (exclude the id), it always throw the NOT NULL constraint error. If I'm not mistaken, Primary Key will automatically auto_increment means that I don't really need to provide id for inserting a new row, is this right?

Edit: tried to test the SQL commands in SQLite Playground and it works. I think it might have something to do with my syntax or something wrong with the package I'm using (expo-sqlite)

Here are my codes:

useEffect(() => {
    // Create new tables if no database exist
    db.transaction((tx) => {
      tx.executeSql(
        'create table if not exists categories (id integer, name varchar(255), primary key (id));',
        [],
        () => {},
        (tx, err) => {
          console.log('this is the error beginning', err);
          return true;
        }
      );
      tx.executeSql(
        'create table if not exists transactions (id integer, tx_datetime datetime, tx_value varchar(255), note varchar(255), tx_day integer, tx_month integer, tx_year varchar(255), category integer, tx_type integer default 0, primary key (id), foreign key (category) references test_categories (id));',
        [],
        () => {},
        (tx, err) => {
          console.log('this is the error beginning two', err);
          return true;
        }
      );
    });
  }, []);

db.transaction((tx) => {
    // tx.executeSql(
    //   'insert into transactions (tx_datetime, tx_value, note, tx_day, tx_month, tx_year, tx_type, category) values (?,?,?,?,?,?,?,?);',
    //   [
    //     data.tx_datetime,
    //     data.tx_value,
    //     data.note,
    //     data.tx_day,
    //     data.tx_month,
    //     data.tx_year,
    //     data.tx_type,
    //     data.category,
    //   ],
    //   () => {},
    //   (tx, err) => {
    //     console.log('add error', err);
    //     return true;
    //   }
    // );
    tx.executeSql(
      'insert into categories(name) values("testing");',
      [],
      () => {},
      (tx, err) => {
        console.log('errro again', err);
        return true;
      }
    );
  });

CodePudding user response：

Honestly, not really sure what fixes it. But here are the new code that I'm using. Probably deleting and creating a new table with the correct query does the job like what @forpas said in the comment.

 db.transaction((tx) => {
      tx.executeSql(
        'create table if not exists categories (id integer primary key not null , name varchar(255));',
        [],
        () => {
          console.log('this is created');
        },
        (tx, err) => {
          console.log('this is the error beginning', err);
          return true;
        }
      );
      tx.executeSql(
        'create table if not exists transactions (id integer primary key, tx_datetime datetime, tx_value varchar(255), note varchar(255), tx_day integer, tx_month integer, tx_year varchar(255), category integer, tx_type integer default 0, foreign key (category) references test_categories (id));',
        [],
        () => {
          console.log('table two created');
        },
        (tx, err) => {
          console.log('this is the error beginning two', err);
          return true;
        }
      );
    });

and here's the second one

  tx.executeSql(
      'insert into categories (name) values (?)',
      ['testing'],
      () => {},
      (tx, err) => {
        console.log('errro again', err);
        return true;
      }
    );