Auto_increment throws errors with CREATE TABLE
I am using netbeans as a tool for my java and I have a problem. I read this tutorial and then I tried to create a table using this SQL:
CREATE TABLE CUSTOMERS (
ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
ADDRESS VARCHAR(30),
CITY VARCHAR(30),
STATE_ VARCHAR(30),
ZIP VARCHAR(15),
COUNTRY_ID INTEGER,
PHONE VARCHAR(15),
EMAIL_ADDRESS VARCHAR(50)
)ENGINE=INNODB;
When I tried to run it I got this error message:
Sql state 42X01: Syntax error: encountered "AUTO_INCREMENT" on line 2 column 29
and when i remove AUTO_INCREMENT another error:
detected MOTOR = INNODB;
Can anyone help me? Thank.
my sugestion would be as follows
CREATE TABLE CUSTOMERS
( ID INTEGER NOT NULL auto_increment,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
ADDRESS VARCHAR(30),
CITY VARCHAR(30),
STATE_ VARCHAR(30),
ZIP VARCHAR(15),
COUNTRY_ID INTEGER,
PHONE VARCHAR(15),
EMAIL_ADDRESS VARCHAR(50),
PRIMARY KEY (ID));
Don't know what the engine = innodb is for, have you tried without it?
source to share
The "engine = innodb" part indicates the database engine that is used in the database. With MySQL, you can specify different engines like "InnoDB", "MyISAM", etc. They have different properties and functions - some of them allow foreign indices and some do not. Some have different locking mechanisms, some of which have different atomic / rollback properties. I don't know the details, but if you want a really high performance database setup, you should research which engine is best for each type of table you create. Also, all database experience has been with MySQL and I'm not sure if this is what you are using.
source to share