Introduction
Another article where the main focus is just to show how to solve error message. The error message appears upon creating a new table. But the one which is triggering the error message is in the usage of the ‘auto_increment’ in the query as exist below :
db_apps=# create table users(user_id int auto_increment, email_id varchar(55), password varchar(55), first_name varchar(55), last_name varchar(55), primary key(user_id)); ERROR: syntax error at or near "auto_increment" LINE 1: create table users(user_id int auto_increment, email_id varc... ^ db_app=#
Solution
So, after getting the description for all the error message above in the previous part. The solution for adding an auto increment feature is for set the sequence to the table. Just create the table as is without having to add the ‘auto_increment’ reserved keyword for adding the auto increment function. Actually, the solution is actually exist in the previous article. Not a direct solution for solving the problem in order to add the auto_increment feature but the focus is to create the table successfully. That article for creating the table has the title of ‘How to Solve Error Message Cannot Create user Table in PostgreSQL Database Server’ exist in this link. The actual solution after creating the table is in the following steps :
-
The following is the command for creating the table as follows :
db_apps=# create table users(user_id int primary key, email_id varchar(55), password varchar(55), first_name varchar(55), last_name varchar(55)); CREATE TABLE db_apps=#
-
Create a new sequence in the PostgreSQL command console. It is actually already available in another article with the title of ‘How to Alter Primary Key Column as an Auto Increment Column in PostgreSQL Database Server’ in this link. Just type the following syntax pattern :
product=# create sequence user_id_seq; CREATE SEQUENCE product=#
-
Finally, continuing on the previous step, for creating the sequence, just alter the primary key column. It is an additional step in order to modify that primary key column to have an auto increment feature. Just execute the following command :
product=# alter table category id set default nextval('user_id_seq'); ALTER TABLE product=#
Incorrect syntax near ‘auto_increment may arise due to the factor that the SQL does not support Auto increment. The solution to the error is easy and requires the user to log in through MySQL.
Bobcares answers all questions no matter the size, as part of our Server Management Services
Let us take a look at the auto-increment error and its solution in detail
Incorrect syntax near ‘auto_increment’ Error
The main reason to get an error on AUTO_INCREMENT as SQL server does not support Auto-increment. SQL Server, like any other database, does not support single quotes in column names. For example, consider the table given below:
CREATE TABLE Invoice(
Invoice_No INT NOT NULL AUTO_INCREMENT,
Order_ID INT NOT NULL,
TotalPrice VARCHAR(30) NOT NULL,
Quantity VARCHAR(30) NOT NULL,
PRIMARY KEY (Invoice No),
FOREIGN KEY (Order_ID) REFERENCES OrderInfo (Order_ID) );
This table will generate the Incorrect syntax near the 'AUTO_INCREMENT'. error
The solution to the error
Firstly make sure to use MySQL, because AUTO INCREMENT doesn’t work with other DBs like SQL Server (use Identity(1, 1) instead). Secondly, when marking it as the PK, use Invoice No rather than Invoice No. Finally, Declare explicitly whether NAME is NULL or NOT NULL, removing the user’s reliance on the current connection settings.
So considering the above-mentioned solutions rewrite the table into the following:
CREATE TABLE sqlalchemy_generic_types ( sqlalchemy_generic_type_id INT IDENTITY(1, 1) PRIMARY KEY,
ObjectName VARCHAR(25) NOT NULL,
Description VARCHAR(100) NOT NULL
);
Take note of the modifications given to the table given above:
- The id assigns an increasing value by IDENTITY(). The id will have a descriptive name.
- Because the space has been removed from ObjectName, the name does not need to be escaped.
- To define the table, no escape characters are required.
[Need assistance with similar queries? We are here to help]
Conclusion
To conclude it is easy to manage and solve the ‘incorrect syntax near auto_increment’ error. The error might occur due to the fact that SQL does not support Auto increment put in a single column.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
I get the error on build :
Server failed to start due to error: SequelizeDatabaseError: syntax error at or near «SERIAL»
This error ONLY appears when the parameter autoIncrement=true is given to the primary key.
'use strict';
export default function(sequelize, DataTypes) {
return sequelize.define('Ladder', {
ladder_id: {
type: DataTypes.UUID,
allowNull: false,
primaryKey: true,
autoIncrement: true //<------- If commented it works fine
},
ladder_name: {
type: DataTypes.STRING(50),
allowNull: false,
unique: true
},
ladder_description: {
type: DataTypes.TEXT,
allowNull: true
},
ladder_open: {
type: DataTypes.BOOLEAN,
allowNull: false
},
ladder_hidden: {
type: DataTypes.BOOLEAN,
allowNull: false
},
ladder_creation_date: {
type: DataTypes.DATE,
allowNull: false
},
ladder_fk_user: {
type: DataTypes.INTEGER,
allowNull: false
},
ladder_fk_game: {
type: DataTypes.UUID,
allowNull: false
},
ladder_fk_platforms: {
type: DataTypes.ARRAY(DataTypes.UUID),
allowNull: false
}
},
{
schema: 'ladder',
tableName: 'ladders'
});
}
I have Sequelize 3.30.4 and postgreSQL 9.6.
I want autoIncrement at true because I am generating the UUID with postgreSQL uuid_generate_v4().
Not a regular sequelize user here but let me point out that using autoIncrement for non sequential column is not the right way in postgreql. Postgresql does not provide a default uuid number generator but an extension can be added easily https://www.postgresql.org/docs/9.4/static/uuid-ossp.html. I believev you have already done so.
The next step then is to us the sequelize.fn function.
Creates an object representing a database function. This can be used in search queries, both in where and order parts, and as default values in column definitions.
so we have
ladder_id: {
type: DataTypes.UUID,
allowNull: false,
primaryKey: true,
default: sequelize.fn('uuid_generate_v4')
}