Hacker News Reader

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed.

Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1]

    INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

[1] https://www.postgresql.org/docs/current/sql-insert.html

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente...

There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link:

* The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT.

* The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.

> PostgresSQL isn’t SQL though

What is SQL acc. to you? MS-SQL, MySQL, SQLITE, Bigquery “Standard SQL”?

Standards published by ISO/IEC.

Sure, but where is that pure standard implemented?

Why does it need to be implemented somewhere?

What do you mean?

If the ISO SQL is not implemented by any SQL DB, and you denounce PostgreSQL/etc version of SQL - what is left over?

I’m not denouncing PostgreSQL. There’s things that are appropriate for a standard and other, sometimes far less elegant, things that are appropriate for a concrete implementation. The linked article propose a change to the standard. If it had only proposed a non-standard vendor extension for some or other database we wouldn’t be having this discussion.

That's DML, not DQL. Still, I disagree with GP's arguments (see separate comment).

> That's DML, not DQL.

Thanks. I incorrectly assumed there were only two sublanguages. But now when you say it, I've heard both DML and DDL before, but never DQL. Curious if there were even more categories, I found this Wikipedia article:

https://en.wikipedia.org/wiki/Data_query_language

Interesting to read, what a SELECT statement is, depends on if having FROM or WHERE "data manipulators". Quote from Wikipedia:

"Although often considered part of DML, the SQL SELECT statement is strictly speaking an example of DQL. When adding FROM or WHERE data manipulators to the SELECT statement the statement is then considered part of the DML."

> and produce correct results

This doesn't interfere with that.

> relying only on the structure and content of tables

Constraints are part of the table schema, not index schema.

> his proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed.

Not allowed says who? The standard? I doubt it, and anyways, it can be changed.

"That's not allowed" is not a good argument. A better argument is that this is the first time a constraint can change the meaning of a query -- that is a good argument, but it would be better if the constraint could change existing queries, which it does not do. Because this would only affect queries that refer to the constraint, this seems quite allowable to me.

> More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, ...

This is not about optimization, ergo this argument is out.

I'm not sure I want this particular extension, but I don't buy your arguments against it.

> More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful.

Constraints are part of the logical model just like the types of columns are. Indexes on the other hand is part of the physical model and should be transparent to the logical model. Database engines tend to couple foreign-key constraints with indexes, since you usually want an index on a foreign-key. But in principle they are separate.

So I don't see any violation of the relational model in this proposal. I do like the general idea of extending SQL with metadata-aware abstractions, although I'm not a fan of this particular syntax.

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement

This doesn't break that constraint in an SQL RDBMS that also implements the relational model, since it is a fundamental element of the relational model that schema metadata is stored as data, and therefore constraint specifications, including names, are included within “content of tables”.

To me, making the data relationships implicit (and making them explicit again in the DAO layer of your app) is a nightmare from a data design point of view. In real life, a DB is often tied to the app that was built on top of it. Making data reuse, data discovery and data understanding VERY hard.

I agree with all the points you make here, we’ll said.