Leaderboard

@SaranacLake Your summary is great. Requinix makes some good points. While MySQL does conflate the terms Key and Index in DDL as alternative ways to achieve the same physical thing, there really is no relational concept of a "key". Only a "Primary Key" or "Foreign Key". The other thing about MySQL we have covered previously, is the importance of the InnoDB engine for providing referential integrity, and ACID. It also does row level locking whereas MyISAM only has table locking, albeit very fast table locking. What this means is that your DDL that defines a table might be something like this: CREATE TABLE IF NOT EXISTS checklists ( todo_id INT AUTO_INCREMENT, task_id INT, todo VARCHAR(255) NOT NULL, is_completed BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (todo_id , task_id), FOREIGN KEY (task_id) REFERENCES tasks (task_id) ON UPDATE RESTRICT ON DELETE CASCADE ) engine=MyISAM; This DDL will run without issue, regardless of the existence or lack thereof, of a tasks table. For a long time, MySQL defaulted to the MyISAM engine, so even without the engine statement, it would run, discarding the foreign key constraint that would be created or checked for validity with the InnoDB engine. You can see the available engines and the default by issuing: SHOW ENGINES\G or SHOW ENGINES

2) Only if you used a FOREIGN KEY clause with the table. Otherwise it's just a concept (see #5). 5) No. A "key" is not a real thing. An index is a real thing, a constraint is a real thing, a primary key and a unique key are real things, a foreign key may or may not be a real thing (see #2), but a mere "key" is just a concept. 6) No. An index can exist on a column without that column being a "key" or having a constraint.

Reminder: these forums use Let's Encrypt certs. They last for 3+ months and we have a cronjob that automatically renews when needed. A free cert that I never have to worry about expiring and renewing by myself is amazing.