SQL Server 2019 ProgrammingError: Incorrect syntax near 'AUTO_INCREMENT'

Question

I'm trying to figure out why I'm getting this error and how to solve it.

The code:

CREATE TABLE sqlalchemy_generic_types 
(
    'No.' INT NOT NULL AUTO_INCREMENT,
    'Object Name' VARCHAR(25) NOT NULL,
    'Description' VARCHAR(100) NOT NULL,
    PRIMARY KEY('No.')
);

The error:

(pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'AUTO_INCREMENT'. (102) (SQLExecDirectW)") [SQL: /* Creating a table */ CREATE TABLE sqlalchemy_generic_types ( 'No.' INT NOT NULL AUTO_INCREMENT, 'Object Name' VARCHAR(25) NOT NULL, 'Description' VARCHAR(100) NOT NULL, PRIMARY KEY('No.') );] (Background on this error at: http://sqlalche.me/e/13/f405)

Answer 1

SQL Server doesn't support auto-increment. Nor does SQL Server -- or any other database -- support single quotes for column names (as far as I know).

I would recommend writing the statement as:

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

Note the changes:

• IDENTITY() is assigns an increasing value to the id.
• The id is given a meaningful name.
• The space is removed from ObjectName, so the name does not need to be escaped.
• No escape characters are needed to define the table.