SQLite Create Table Query does not create a table-CodePudding

This simple sqlite query:

CREATE TABLE customers 
(
    id INT(255) NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255),  
    email VARCHAR(255),  
    password VARCHAR(255), 
    PRIMARY KEY (id)
);

is not working.

CodePudding user response：

This is the correct syntax for the definition of the table:

CREATE TABLE customers ( 
  id INTEGER PRIMARY KEY AUTOINCREMENT 
  name TEXT,  
  email TEXT,  
  password TEXT
);

SQLite does not support AUTOINCREMENT when the column's data type is other than INTEGER and the column is not the PRIMARY KEY of the table.
Also there are no int(255) and VARCHAR data types in SQLite (although you can use it in the CREATE TABLE statement). Use TEXT instead of VARCHAR.

See the demo.

CodePudding user response：

SQL lite doesn't support cahrchar or auto increment (at least not without rowid

see manual

CREATE TABLE t(id INTEGER PRIMARY KEY ASC, name TEXT , email TEXT ,  password TEXT);

INSERT INTO t (name,email, password) VALUES ('abc','bcd','efg')

INSERT INTO t (name,email, password) VALUES ('abc2','bcd2','efg2')

SELECT * FROM t;

id | name | email | password
-: | :--- | :---- | :-------
 1 | abc  | bcd   | efg     
 2 | abc2 | bcd2  | efg2

db<>fiddle here