CODEWITHSUNDEEP
sql-server
Thomas Adema
Thomas Adema

Reputation: 13

Syntax Error executing Create Table

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?

Upvotes: 1

Views: 231

Answers (2)

Hasan Fathi
Hasan Fathi

Reputation: 6106

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

Upvotes: 1

Raymond Nijland
Raymond Nijland

Reputation: 11602

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

Upvotes: 1

Related Questions