SQL Syntax Error with CREATE TABLE

Question

I get a SQL Sytax Error when I try to execute this code:

CREATE TABLE ? (
ID INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`UUID` VARCHAR(36) NOT NULL
);

I use a PreparedStatement to replace the ? with a String

The Error Message:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an errorin your SQL syntax; check the manual that corresponds to your MySQL serverversion for the right syntax to use near ''95f7ed55-ab3d-46f9-bffe-72bf5780a1ec' (ID INT(255) UNSIGNED AUTO_INCREMENT PRIM' at line 1

Thanks for your help!

Identifiers may begin with a digit but unless quoted may not consist solely of digits. dev.mysql.com/doc/refman/5.0/en/identifiers.html — Abhik Chakraborty
– Abhik Chakraborty, Commented Jun 5, 2015 at 10:55
It looks like you're using a single-quote to specifying the table name '. Try using a backtick instead and see if this resolves it. — Martin
– Martin, Commented Jun 5, 2015 at 10:56
Can you please put your whole code, creating DDL using dyanmic sql? — Anil
– Anil, Commented Jun 5, 2015 at 11:00
You cannot use parameters for object names like a table name. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 5, 2015 at 13:43

pavel · Accepted Answer · 2015-06-05 10:56:21Z

2

Put table name into backticks, it contains - which has to be escaped.

You used single quotes ('), which are bad in SQL.

answered Jun 5, 2015 at 10:56

pavel

27.3k11 gold badges47 silver badges63 bronze badges

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

Comments

Roel · Accepted Answer · 2015-06-05 11:22:33Z

The sign (-) character is not allowed when not using quotes. http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)

This is executed now:

CREATE TABLE 95f7ed55-ab3d-46f9-bffe-72bf5780a1ec (
ID INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`UUID` VARCHAR(36) NOT NULL
);

But the identifier on the first line needs to be quoted:

CREATE TABLE `95f7ed55-ab3d-46f9-bffe-72bf5780a1ec` (

Maybe something like this will do the trick? (edit: added "IF NOT EXISTS" from Anil Kumar's answer)

CREATE TABLE IF NOT EXISTS `?` (
ID INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`UUID` VARCHAR(36) NOT NULL
);

ughai · Accepted Answer · 2015-06-05 11:25:02Z

1

Please try following query syntax:

CREATE TABLE table_name (ID INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,UUID VARCHAR(36) NOT NULL);

edited Jun 5, 2015 at 11:25

ughai

9,9004 gold badges33 silver badges48 bronze badges

answered Jun 5, 2015 at 11:08

Snehal Mulay

112 bronze badges

1 Comment

ughai Over a year ago

Can you explain how this answers the question?

Karthik Keyan · Accepted Answer · 2015-06-05 10:56:53Z

0

pls try this code.

        CREATE TABLE `table_name`(
       `ID` INT( 255 ) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
       `UUID` VARCHAR( 36 ) NOT NULL
        );

Note: table_name using this symbol.

answered Jun 5, 2015 at 10:56

Karthik Keyan

4264 silver badges15 bronze badges

Comments

Anil · Accepted Answer · 2015-06-05 11:04:05Z

0

-- DROP PROCEDURE IF EXISTS createTblDynamically;
DELIMITER //
CREATE PROCEDURE createTblDynamically(tblName VARCHAR(255))
BEGIN
    SET @tableName = tblName;
    SET @q = CONCAT('
        CREATE TABLE IF NOT EXISTS `' , @tableName, '` (
            ID INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`UUID` VARCHAR(36) NOT NULL
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    ');
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END //

answered Jun 5, 2015 at 11:04

Anil

3,7522 gold badges27 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQL Syntax Error with CREATE TABLE

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related