Syntax Error executing Create Table

Question

I have a Query I want to run to create a Table users but when executing the query I get this error: Started executing query at Line 1 Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ''. Total execution time: 00:00:00.031`

This is my Query:

    CREATE TABLE `users` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(30) NOT NULL,
  `password` varchar(32) NOT NULL,
  `secret` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`)
);

Could anyone let me know why I get this error when running my Query?

mysql or sql-server? Did you even know there is a difference? — B001ᛦ
– B001ᛦ, Commented Feb 6, 2018 at 16:26
create table in sql server or mysql RDBMS, witch you are using? — Hasan Fathi
– Hasan Fathi, Commented Feb 6, 2018 at 16:27
Topicstarter is using SQL-server Msg 102, Level 15, State 1, Line 1 is a SQL-server error message.. MySQL syntax on a SQL-server does not work. — Raymond Nijland
– Raymond Nijland, Commented Feb 6, 2018 at 16:28
How did you run this query exactly? Was it from some query tool or grammatically via JDBC/ODBC Driver? Also was it mysql or sql-server? — seiya
– seiya, Commented Feb 6, 2018 at 16:28

Raymond Nijland · Accepted Answer · 2018-02-06 16:49:13Z

1

You are trying to run MySQL syntax on a SQL-server.

This is the rewritten SQL-Server syntax.

SQL-server does not support backticks (') so i have removed them.
SQL-server does not support AUTO_INCREMENT instead SQL-server is using IDENTITY.
SQL-server does not support unsigned so i have removed it.
SQL-server does not support int(8) so ive changed it into int only.

CREATE TABLE users (
    id int NOT NULL IDENTITY(1,1),
    login varchar(30) NOT NULL,
    password varchar(32) NOT NULL,
    secret varchar(16) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (login)
);

Edit

With naming the indexes.

CREATE TABLE users (
    id int NOT NULL IDENTITY(1,1),
    login varchar(30) NOT NULL,
    password varchar(32) NOT NULL,
    secret varchar(16) NOT NULL,   
    CONSTRAINT PK_ID PRIMARY KEY (id),
    CONSTRAINT UK_login UNIQUE (login)
);

edited Feb 6, 2018 at 16:49

answered Feb 6, 2018 at 16:32

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Raymond Nijland Over a year ago

Then consider to accept this answer.. meta.stackexchange.com/questions/5234/…

GuidoG Over a year ago

Naming your primary key and unique constraint would be even better. Let's learn new people in sql server how to do it correct from the start...

Raymond Nijland Over a year ago

yes indeed @GuidoG iám more into MySQL database myself.. Naming the indexes/constraints would result in better error messages and better names into query plans.

GuidoG Over a year ago

MySql does not allows to name indexes like the primary key ?

Raymond Nijland Over a year ago

Yes it does but the error messages and in the query plans in MySQL are more clear..

Hasan Fathi · Accepted Answer · 2018-02-06 16:53:31Z

1

I think you are trying to use MySql syntax in Sql Sever, Sql server raise error for using (`) character.

use syntax of this examples:

MySQL:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID),
    PRIMARY KEY (ID)
);

SQL Server:

CREATE TABLE Persons (
    ID int NOT NULL IDENTITY(1,1),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person_ID PRIMARY KEY (ID),
    CONSTRAINT UC_Person_LastName UNIQUE (LastName)
);

edited Feb 6, 2018 at 16:53

answered Feb 6, 2018 at 16:35

Hasan Fathi

6,1764 gold badges49 silver badges67 bronze badges

5 Comments

Hasan Fathi Over a year ago

@Thomas i hope this is helpful for you

GuidoG Over a year ago

why dont you also name your primary key ?

Hasan Fathi Over a year ago

your question is "why not using name for primary key?

GuidoG Over a year ago

No, why do you not give a Name to the primary key. The primary key of this table is not named, which will look bad in any error messages

GuidoG Over a year ago

much better +1 from me

Collectives™ on Stack Overflow

Syntax Error executing Create Table

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related