sql syntax error with VERY basic command? why?

Question

I am trying just create a table using MySql Workbench. Here's the sql command:

CREATE TABLE `bmxData`.`new_table` ();

yet I get this error while executing this :-

Executing:

CREATE TABLE bmxData.new_table ();

Operation failed: There was an error while applying the SQL script to the database.

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

SQL Statement:

CREATE TABLE bmxData.new_table ()

Any idea why? I have no idea what I'm doing wrong...

Answer 1

You need to add at least one column:

CREATE TABLE `bmxData`.`new_table`(col INT);

Some RDBMS allow to define table without explicit user column like PostgreSQL:

CREATE TABLE tab();

DBFiddle Demo

Answer 2

There is some discussion in relational theory of the meaning of tables with zero columns. Also described as relations of zero degree.

Relational theory researcher C. J. Date refers to TABLE_DUM and TABLE_DEE. Both have no columns, but the difference is that TABLE_DUM has no columns and no rows, whereas TABLE_DEE has one row (even though that row has no columns).

See this excerpt from "Database in Depth: Relational Theory for Practitioners": https://flylib.com/books/en/2.214.1.38/1/

The following query will therefore return no rows:

SELECT * FROM TABLE_DUM CROSS JOIN MyTable;

Whereas this query will return the same rows and columns as MyTable:

SELECT * FROM TABLE_DEE CROSS JOIN MyTable;

So there's some precedent and significance for a table with no columns. You can think of it by analogy as the role of 0 and 1 in multiplication:

• 0 x any number is 0
• 1 x any number is the same number

HOWEVER, standard SQL doesn't allow it. SQL doesn't exactly implement all of the concept of relational theory faithfully. In this case, standard SQL defines a table as having at least one column. The designers of SQL decided that a table with no columns isn't so interesting as to justify support in the SQL language.

If PostgreSQL or some other implementation of SQL decides to allow a table with no columns, they are doing it as an extension to standard SQL.