Can't create table. Stupid issue, simple query

Question

CREATE TABLE `2m_aperturas_credito` (
  `idaperturacredito` int(10) NOT NULL AUTO_INCREMENT,
  `nombreapertura` varchar(100) NOT NULL,
  `activo` int(10) NOT NULL DEFAULT '1',
  PRIMARY KEY (`idaperturacredito`),
  KEY `idaperturacredito` (`idaperturacredito`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

This gave me an error:

[Err] 1005 - Can't create table 'mydb.2m_aperturas_credito' (errno: -1)

What can be wrong here??

Emm, maybe wrong database? Are you sure you are putting this into "mydb" database? Also fix the AUTO_INCREMENT to 0 or 1, not sure. I think you want to start the database counting from 1 right? — Tomáš Aresak Malčánek, Oct 01 '14 at 15:30
i think you can't start a table name with a number, see this thread: http://stackoverflow.com/questions/4200351/what-characters-are-valid-in-an-sql-server-database-name — Groben, Oct 01 '14 at 15:31
@Groben The answer in that link seems to contradict what you say. That link also refers to sql server. — Jim, Oct 01 '14 at 15:38
could it be a constraint issue? and are you sure the table doesn't already exist...? maybe? — gloomy.penguin, Oct 01 '14 at 15:40
there is no contradiction: "When the compatibility level is 100 (which, according to SQL Server Management Studio, means "SQL Server 2008"), the name must start with a Unicode letter, _, @, or #; followed by one or more letters, numbers, @, $, #, or _." It must not start with a number. You have better explanations here: http://stackoverflow.com/questions/15917064/table-or-column-name-cannot-start-with-numeric — Groben, Oct 01 '14 at 15:40
[If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.](http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html)(seems unlikely? but that's a link to official docs) — gloomy.penguin, Oct 01 '14 at 15:44
Probably a corrupted tablespace. See https://stackoverflow.com/questions/3218872/delete-a-crashed-innodb-table — Bill Karwin, Oct 01 '14 at 15:56

score 1 · Answer 1 · answered Oct 01 '14 at 15:48

Quoting from the Reference manual: Schema Object Names:

It is recommended that you do not use names that begin with Me or MeN, where M and N are integers. For example, avoid using 1e as an identifier, because an expression such as 1e+3 is ambiguous. Depending on context, it might be interpreted as the expression 1e + 3 or as the number 1e+3.

That said, as a general rule, avoid using numbers at the start of an object name (databases, tables, columns, procedures, functions, variables, etcetera). Try creating the table without the number.

Can't create table. Stupid issue, simple query

1 Answers1