using spaces in columns names in sql to create table

Question

I am trying to create table by sqlyog i want to use spaces in columns names but i still got errors for example id number i just can do it like this id_number i searched in this website i found two ways [id number] or "id number" i tried it but i still have errors

this is the code

   CREATE TABLE project(
ProjectID VARCHAR(10),
Project NAME VARCHAR(50),
Group_Name VARCHAR(20),
BeginDate VARCHAR(10),
EndDate VARCHAR(10)
);

and these are the errors that i got them

   1 queries executed, 0 success, 1 errors, 0 warnings

Query: CREATE table project( ProjectID varchar(10), Project Name varchar(50), Group_Name varchar(20), BeginDate varchar(10), EndDate va...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Name varchar(50),
Group_Name varchar(20),
BeginDate varchar(10),
EndDate varc' at line 3

i hope some one helping me

See also: https://stackoverflow.com/questions/14190798/how-to-select-a-column-name-with-a-space-in-mysql — Andy Lester, Mar 07 '19 at 17:27
Are you sure you do want it? Cannot it create future maintenance pain? Keep it simple and stupid — jean, Mar 07 '19 at 17:29

score 1 · Answer 1 · answered Mar 10 '19 at 21:28

The SQL Standard defines double quote character " to delimit identifiers. Speaking of MariaDB and MySQL this requires that the sql_mode was set to ANSI:

mysql> set sql_mode=ANSI;
Query OK, 0 rows affected (0.00 sec)

mysql> create table project("project name" varchar(50));
Query OK, 0 rows affected (0.02 sec)

Another option (as mentioned in the previous answer) is to use backticks. However this solution will not be portable.

score 0 · Answer 2 · answered Mar 07 '19 at 17:26

0

You can create tables and columns with spaces in the name using backticks (`)

Egg:

CREATE TABLE `project project` 
(
`ProjectID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
`Project NAME` VARCHAR(255) NOT NULL
    .
    .
    .
);

answered Mar 07 '19 at 17:26

israel3D

1

score 0 · Answer 3 · answered Mar 07 '19 at 17:26

0

try like below

CREATE TABLE project(
ProjectID VARCHAR(10),
`Project NAME` VARCHAR(50),
Group_Name VARCHAR(20),
BeginDate VARCHAR(10),
EndDate VARCHAR(10)
);

answered Mar 07 '19 at 17:26

Zaynul Abadin Tuhin

31,407
5
33
63

using spaces in columns names in sql to create table

3 Answers3