Key vs. Index

[SaranacLake]

Sorry for all of the questions, but as I was reading up on naming conventions for Indexes and Constraints, I have come across lots of other material that leads to even more questions!

What exactly is the difference between a database (generic) "KEY" and a database "INDEX"?

This is my understanding...

A "KEY" is how you join tables together.  So, you might have a "PRIMARY KEY" (unique) for the parent entity, and then a "FOREIGN KEY" (not unique) for the child entity which ties back to the parent entity.  "KEYS" are all about linking tables.  You also have "UNIQUE KEYS" (unique but allow Nulls).

 

An "INDEX" is designed to speed up queries.  It does not have anything to do with table joins.

 

Where it gets confusing is that there seems to be some overlap.  For instance, a "PRIMARY KEY" also has an "UNIQUE INDEX" built into it.  And when you create a "FOREIGN KEY (Constraint) in MySQL, you are also creating a "(Foreign key) INDEX).

 

And I understanding the concepts of a "KEY" and an "INDEX" correctly??

 

P.S.  I believe that a "Primary Key" and a "Foreign Key" are more correctly called "CONSTRAINTS" as in a "Primary Key Constraint" and a "Foreign Key Constraint", right?

 

 

 

 

 

 

Edited February 23, 2020 by SaranacLake

[Barand]

Below is an extract from the mysql reference manual

As you can see, with the exceptions of "PRIMARY KEY" and "FOREIGN KEY" (which can be considered to be reserved phrases) the terms "index" and "key" are interchangeable.

Edited February 24, 2020 by Barand

[requinix]

On the common parlance side of things, typically "index" emphases that it's related to searching while "key" refers to it being an identifier for a record. Like, if your table has a user ID key then it should also have a user ID index.

[SaranacLake]

13 hours ago, Barand said:

Below is an extract from the mysql reference manual

As you can see, with the exceptions of "PRIMARY KEY" and "FOREIGN KEY" (which can be considered to be reserved phrases) the terms "index" and "key" are interchangeable.

 

Okay, maybe that is so in MySQL, but in general database terms a "key" and and "index" are NOT the same thing!

I had a chance to read some more on this topic, and just ponder things, and I think MySQL and people in general use these terms loosely without fully comprehending them...

 

 

[SaranacLake]

4 hours ago, requinix said:

On the common parlance side of things, typically "index" emphases that it's related to searching while "key" refers to it being an identifier for a record. Like, if your table has a user ID key then it should also have a user ID index.

Based on my research last night, I would say that...

A Primary Key is a CONSTRAINT.  (But it also has an Index built into it automatically.)

A Foreign Key is a CONSTRAINT.  (But it also has an Index which you can define with the same name as the Constraint or the Index can have its own name.)

A Unique Key is a CONSTRAINT.  (But it also includes an Index built into it - at least with MySQL.)

In MySQL 5.7, INDEX types include: Primary, Index, Unique, Spatial, Fulltext

A straight up "Index" is a pure INDEX with no Constraint.  (As mentioned above, Primary Key, Foreign Key and Unique Key/Index are CONSTRAINTS that also have INDEXES - a "combo deal".  🙂

I think in Oracle the Primary Key and Foreign Key forces you to set up the CONSTRINT and INDEX separately, but I'm not entirely sure.

In summary, people use these terms pretty loosely - especially with MySQL - but there are clear differences as to what each things is and does.

CONSTRAINTS constrain/restrict/limit...

INDEXES speed up queries with multiple JOINs, as well as speed up searching within a table.

 

 

 

 

 

[gizmola]

A "Key" is not a constraint.

There are 2 types of keys.  "Primary Key" or "Foreign Key".

In a table the Primary key is one or more columns in combination, that can be used to uniquely identify a single row in the table.

A "Foreign Key" is the "Primary Key" of another table, that establishes a relationship to a single row in the Foreign table.

Constraints are restrictions to the overall "Domain" of acceptable values allowed in a particular column.

For example, you could have a constraint defined for a "gender" varchar that allows ("male", "female", "unspecified") only.

In practice there are inherent constraints for primary and foreign keys.

For a primary key, a "Unique" constraint is assumed, as no 2 rows in a table can have the same primary key.

For a foreign key, there is an implied "referential integrity constraint" that enforces the rule that any value for the foreign key column (or set of columns) must have a corresponding Primary key in the foreign table.  The association of these implied constraints is typically referred to as "Declarative referential integrity" in that when you specify the primary and foreign keys, the constraints are created automatically.  With MySQL referential integrity requires a supported engine, typically InnoDB.  Pluggable (optional) engines are one of the things that sets MySQL apart from the other major RDBMS.

Indexes provide performance for relational joins and searching

In practice, all RDBMS use indexes for performance AND some constraints.  For example, when you define a key, an associated Index is automatically created for you.  This is true of all the major RDBMS, so MySQL is not different from Oracle, Sybase etc.  You certainly can index a column or set of columns without that index being associated with another table as a foreign key.  So in that way, I agree that Keys and Indexes are not the same thing, so long as you understand that if you define a key an Index is being created.

The RDBMS is inherently going to use the key indexes it creates in numerous ways, for performance, constraints and primary key uniqueness.  

The authority for this terminology is E.F. "Ted" Codd, who was the IBM research fellow that invented the Relational Model for databases, rules of database normalization, SQL etc., upon which all RDBMS are based.

 

[SaranacLake]

@gizmola,

 

On 2/26/2020 at 6:14 PM, gizmola said:

A "Key" is not a constraint.

What if I had said...

1.) "A Primary Key is a key that also HAS A constraint and HAS AN index built into it automatically by MySQL."

2.) "A Foreign Key is a key that also HAS A constraint and HAS AN index built into it automatically by MySQL."

3.) "A Unique Key is a key that also HAS A constraint and HAS AN index built into it automatically by MySQL."

 

Also...

4.) "A Key is a KEY, but if it is a Primary/Foreign/Unique Key, then it also comes with a CONSTRAINT and an INDEX."

5.) "A Key is NOT an "Index", but it comes with one built in for performance reasons."

6.) "An Index can exist on a column WITHOUT having an associated Constraint or Key."

 

Agree or disagree?
 

[requinix]

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.

[gizmola]

@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

 

[SaranacLake]

Thanks for the thoughts and feedback, @requinix and @gizmola

Trying my best to learn things the correct way so that I don't cause bigger issues down the road!

This is good information to know!