Comment by dragonwriter | Hacker News Reader

Comment by dragonwriter | Hacker News Reader

Parent Full thread

> And I have hard time thinking of how exactly SQL, or any programming construct, can make such logical (but not syntactical) errors evident to the novice.

SQL could do one (or both) of these to make that easier:

(1) have a preferred syntax for joins that leveraged declared relationship (FK) that required the referenced column(s) of the FK relationship to be a declared candidate key (either PK or subject to a Unique constraint) -- which would make other joins automatically suspect.

EDIT: Incidentally, I've seen proprietary RDBMS languages and visual interfaces (usually, for systems that also supported SQL) that did this exclusively -- you could only join via declared FK relations. This is safer, but probably overly restrictive for general use.

(2) in something otherwise like the existing join syntax, require an explicit modifier to do a join without a constrained-as-unique column on one side.

Ah, I hadn't thought about teaching the novice user to declare their own foreign keys (assuming that the data workflow starts with importing a plaintext CSV into a database) before doing a JOIN. Such a process would force the user to think about what a foreign key column should contain.

(in the past, I've generally avoided teaching table/data altering statements, such as CREATE/DROP/UPDATE, until after thoroughly covering SELECT)

I use SQLite exclusively for teaching, but one thing I miss from PostgreSQL (in my limited usage of it, at least), is how it throws an error when the user attempts to select columns not referenced in a GROUP BY clause:

   SELECT id, last_name, COUNT(1) AS people_with_that_name
   FROM people
   GROUP BY last_name;

`id` is the problematic column here. But in SQLite and MySQL, the interpreter will dutifully include the column even though it has no meaningful interpretation. Come to think of it, SQLite's simplified data types (including implicit type casting) is kind of a pain when it comes to teaching.

What will the id become in that query when you got multiple people with the name? Null?

Reply on news.ycombinator.com