What is the rationale behind disallowing no-column tables?

Curious. What would be the rationale for allowing them?

To whomever is involved with documentation, the relevant page (https://sqlite.org/lang_createtable.html) does not appear to explicitly state that it is an error to specify no columns. Might be worthwhile to mention that.

At the top of that page, there is a very clear syntax diagram for CREATE TABLE. That diagram shows how such statements can be constructed. There is obviously no way to get from its start point to its end point without having passed thru "column-def" at least once, or thru select-statement (where there is no way to avoid passing thru result-column at least once.)

I notice, from the syntax diagram, that I may not append the word GIRAFFE to the end of an otherwise valid CREATE TABLE statement and expect the SQLite statement compiler to accept it. Without much trouble, I could spend all day devising illegal statements that resemble but do not match the syntax in that diagram. I am not tempted to document any of them.

Is there a reason to favor your one deviation with an assertion that it is disallowed? If such was there in the doc, I would want to see something like "The above represents the set of valid CREATE TABLE statements, without exception." Neither skipping required elements nor prepending or inserting or appending elements not shown will be accepted."

There is an oft-repeated joke among exasperated engineers: "I wanted to put 'This drawing must be followed in every detail.' in the drawing, but could not be sure that would be heeded." (This usually when someone neglected to meet a spec.)

At the top of that page, there is a very clear syntax diagram for CREATE TABLE. That diagram shows how such statements can be constructed. There is obviously no way to get from its start point to its end point without having passed thru "column-def" at least once, or thru select-statement (where there is no way to avoid passing thru result-column at least once.)

While that's true, and in this case meaningful, it has been often repeated that the "railroad" syntax diagrams are not considered definitive in their representation. It isn't difficult to find syntax diagrams that allow for invalid commands. I can't blame someone for looking at them as examples or representations, and not ground-truth.

I thought they were intended to be definitive, just as much so as strung-together words in the docs. If ever either false sentences or false "railroad"^a diagrams are reported here, those will be fixed. Neither should be taken as infallible truth.

It isn't difficult to find syntax diagrams that allow for invalid commands.

As a rule, they specify syntax only. Semantic constraints are a further restriction of the valid statement set, and those constraints should be (and are meant to be) mentioned in the verbiage for particular statement categories. Those are not always right either, but we consider it an error when they are not.

a. We should thank Niclaus Wirth for bringing railroad syntax diagrams to the computing world several decades ago.

Curious. What would be the rationale for allowing them?

While, I must admit, I find the OP's specific need and application design a bit questionable (especially since I've been using SQLite since long before it supported column modification DDL), most RDBMS products do support zero-column tables.

This gets back to the fact that SQL is based on Relational Theory, which in turn is based off set theory, and most of the time sets are allowed to be empty. In Relational Algebra, the concept of a zero-column table (or, more properly, a zero-attribute relation) is so core to the mathematics of Relations, there are actually two named zero-column tables: TABLE DUM (which has no rows) and TABLE DEE (which has a single, empty row). In proper Relational Algebra, rows must be unique, so these are the only two valid zero-column tables. (For those that are not aware, the names are word play off the Tweedledum and Tweedledee characters from Lewis Carroll's Through the Looking-Glass.)

Do they have a practical application beyond mathematical proofs? I'm not as sure about that, especially in the SQL world, which is disappointingly different from an actual Relational system. My only point is that the concept is well accepted, even if it is mostly for theoretical reasons.

For what it's worth, the last time I checked (which was some time ago...) PostgreSQL does allow zero-attribute tables. This should work:

CREATE TABLE dum();
CREATE TABLE dee();
INSERT INTO dee DEFAULT VALUES;

Mathematically and logically and universally (everythingally), the question as "why forbid?" rather than "why allow?"

How close is CREATE TABLE NoColumns(rowid HIDDEN INTEGER PRIMARY KEY); to what you want?

the railroad diagram is insufficient to fully explain the CREATE TABLE syntax, context and gotchas...if it were sufficient, you wouldn't bother with the tonne of explanatory text that follows (including pointing out many error conditions).

Well, yes. I said as much in the post to which you respond. As I tried to say: The railroad diagram depicts required syntax, and the qualifying/explanatory text that follows is there to reveal the semantic constraints upon and purposes of the syntactic elements in the diagram.

What is the rationale for SQLite not supporting zero-column tables? ... Does this just boil down to something jejune, like the whim or ...

Thanks to Jonathan Leffler and Ron Savage, a BNF of the SQL 92 language> can be perused. There, you or anybody else may see that SQL 92 never allowed an empty column list in CREATE TABLE. So that's one reason to not see such an allowance as an important use case. Another would be that it is useless for either queries or storing data. Using such imagination as I can muster, I see it as only useful for automatic SQL generation, and even then only as a theoretic nicety rather than a lamentable missing feature.

As for whimsicality, I have seen virtually nothing like that in the design of SQLite. In fact, as one can see here and here, SQL 99 and 2003 did not allow empty column sets either. So I am not even slightly surprised to see that Richard did not elect to follow the lead of those who decided the feature had some value, for academics or code generator authors.

the documentation could be improved by pointing out that it is an error to not specify any columns. A nice place is a simple sentence in the "3. Column Definitions" section. Why not?

Because it would be redundant. We try to avoid stating the obvious in the interest of not distracting from statements of things not obvious.

If I were imaginative, (and eager to waste time), I could spend day after day coming up with things that the present docs clearly disallow. For example, "One cannot insert "FROMAGE" between CREATE and TABLE." or "The CREATE keyword really, truly and without exception must be present for any hope of table creation to be realistic."

Zero-column tables feel to me like the logical analogue of an empty set, and so much else in mathematics and computer science. It feels inconsistent NOT to support them.

I feel the same way. But that does not lead to any sorrow because I have never had any practical purpose thwarted by this particular departure from ideality.

... imagination ... empathy ...

Your conjecture on this, to the extent not intended as insulting noise, is incorrect. However, I will not argue the point, as it is off-topic and I have better uses for my time.

So what is wrong with CREATE FROMAGE TABLE? Do you hate the French?

;-P

I was catering to some French.

Because it is a corner-case that will have to be extensively tested and debugged and maintained for the next 28 years. Because it is such an odd case, it will be a likely source of bugs. We have been developing SQLite for 22 years now under the assumption that every table has at least one column - who knows how many places there are in the code that make that assumption.

And for what purpose would you have us to undertake this extra work? How is a zero-column table useful in a real-world application?

The risks are too big indeed. And there isn't so much use.

I was just making a point about the fact that logically this is actually not a corner case.

In my case when building "no-code" style frameworks (dynamically generated schema etc) I thought it would be nice to not forbid 0 column tables, just for the sake of not having to do extra checks, for example. Or maybe for other meta applications or whatever is going to demand extra coherence & genericism & universalism in the next years.

But this is just prose and I don't think there is any reason to do this extra work now. (or maybe, make some special hidden column if really necessary, or some other hack, rather that allowing 0 columns)

One thought I have is that a table with no columns is NOT a 'empty' table, but a table that can have any number of empty rows added to it, but then there is, basically by definition, no way to distinguish one row from another. (Maybe like they do for ANY table defined with an explicit Primary Key).

My guess is that systems that DO allow for a columnless table, actually DO add a data column to the table, a hidden primary key, likely an integer, that is just hidden from the user. This gives a 'primary key' defined to allow indexes to locate a given row.

If they don't do that, then it seems almost required that such a table be put into a 'special' state, just for a columnless table, so that when a column IS added, the system can configure the table with some sort of now primary key to access the data. This creates a 'special state' that all the code to access tables needs to consider.

Since you can't add/change the primary key in SQLite after you create the table, it seems to make sense to just start with declaring the column for the primary key to begin with.

One thought I have is that a table with no columns is NOT a 'empty' table, but a table that can have any number of empty rows added to it, but then there is, basically by definition, no way to distinguish one row from another. (Maybe like they do for ANY table defined with an explicit Primary Key).

This is getting way off in the weeds, but does get a bit into the difference between pure Relational Theory compared to actual SQL-- further compared to actual SQL implementations.

As I mentioned, in actual Relational Theory, each row is treated as a set of name/value attributes, and reach table (relation) is a set-of-(row-)sets. Because a set is not allowed to have a duplicate member, this means in Relational Theory that every row, as a whole, must be unique. That is also why, in Relational Theory, there are exactly two possible zero-attribute relations: One with an empty set of rows, and one with the set of exactly one empty row.

SQL, as a language and a system, doesn't require that rows be unique. That said, most SQL engines do, because you have to reference each item somehow. The way they manage this, as you pointed out, is the existence of a "hidden" or "system" column, such as rowid. That also means there is no need for a "special state," because most RDBMS engines will, by default, create a hidden rowid column. It's already there, for every table, in most cases.

I also wouldn't put too much thought into column modification. If you're looking to explore what's "correct," that doesn't really have a bearing, especially for highly theoretical discussions (which is most of what this is). Being able to do column modification is not a requirement of a relational system, it's just really darn handy for real world systems.

My main reason to bring all this up is to point out, once again, that SQL is rooted in Relational Theory. Relational Theory is a formal mathematics system, complete with axioms, theorems, and proofs, that defines information theory and processing. Even though Relational Theory and SQL are not the same, the Venn diagram mostly overlaps; if you want to design really clean databases and schemas, it helps to understand Relational Theory and design within it. I've used many of the proofs and ideas when doing runtime programming as well, such as designing class systems or data structures for application programming. I've also found it a lot easier to understand SQL, data organization, operations, and query design, when you approach it from a Relational Theory context, and then translate it into SQL-- much the same way you might figure out an algorithm or formula using mathematics and algebra, and then translate it in to C to get it working. In specific, I'd super recommend just about any C.J. Date book, especially the titles he did in the "/Theory/in/practice" series from O'Reilly.