Foreign Keys, Indexes & Constraints?

Will I ever get back to being a techie?! :headbang:

Okay, I know what a Primary Key is and what a Foreign Key is.

(After that it all goes dark…)

What is a Foreign Key Index?

What is a Foreign Key Constraint?

What do I need to link my “One” table to my “Many” table and establish a relationship that has “Referential Integrity”?

(I apparently never had to use my brain back in the days when I used MS Access?!)

Now that I have to actually write all of the SQL, this isn’t as easy as I thought!! :frowning:

BTW, I am trying to do this visually using MySQL WorkBench.

TomTees

[quote=“ScallioXTX”]

Need I say more?[/quote]

Yeah, smarty pants, except for the fact that I am getting a good education on all of this, and in the long-run I will be more knowledgeable and better off than those who skim over the details and go for the good stuff… :wink:

TomTees

What does MySQL call the Foreign Key Index?

Can I create a Foreign Key Index manually and override what MyQL does?

Can specify what I want the Foreign Key Index to be named?

an index on a column aids in searches on that column, and a column doesn’t have to be a foreign key to deserve an index,

That much I understand.

but a foreign key needs an index to exist in order to work effectively

And therein lies my confusion…

Because “Key” and “Index” are almost interchangeable, I just always assumed that when you created a Primary Key or a Foreign Key that whatever database you are using would be smart enough to Index them automatically when you create the Key?!

So, am I understanding you correctly in saying…

1.) MySQL automatically create an Index when you create a Primary Key?

2.) MySQL automatically create an Index when you create a Foreign Key?

3.) In MySQL, a “Key” has an “Index” however a “Key” is not an “Index”?? (Maybe that is where I am getting the most confused. I thought they were one in the same w.r.t. to “Keys”?!)

TomTees

I asked because I wasn’t sure if all Indexes show up as tied to the Table they relate to or if they are lumped together in the Database “bucket”.

If they all show up as Database “objects”, then one might want to name they differently versus if they show up as Table “objects” if that even makes any sense?! :scratch:

(Remember, I’m not in mysql or phpMyAdmin yet and am just working and looking and thinking about how all of this Database stuff relates?!)

BTW… do you have a Constraint and Index naming convention that you follow?

TomTees

an index declared on a column which is being used as a foreign key

a foreign key is a kind of constraint – there are other constraints besides foreign key constraints

declare a foreign key in the “many” table

:slight_smile:

there was a time when you had to declare an index on a column before you could create a foreign key using that column, but in recent versions of mysql, you can just declare the foreign key and mysql will build the index silently

an index on a column aids in searches on that column, and a column doesn’t have to be a foreign key to deserve an index, but a foreign key needs an index to exist in order to work effectively

and you can use the terms foreign key and foreign key constraint interchangeably, they are the same thing

So, as usual, I’m getting caught up on details that I need not be so concerned about?! (:

(The way MySQL Workbench displays things really was confusing me earlier today, but I think I have a handle on everything now, and this was still probably a good conversation to have, although it’s not speeding me towards getting my e-commerce site built!!)

what i will often do, if the index is on a single column, is give the index a name of columnname_ix

The convention I have most seen online for constraints/indexes is:

my_index_name_<suffix>

where <suffix> might be _pk, _fk, _ux, _chk, _etc

the only time you will even need the name of an index is if you need to drop it, and at that point, i always just look up the index name first (by doing a SHOW CREATE TABLE on the table), so it doesn’t even have to have a name that i assigned

Good point.

I thought they might get all dumped together on one MySQL table or view and so you could have hundreds of Constraints and Indexes and not know WHAT they are for or WHAT table/column they belong to?!

TomTees

Need I say more? :slight_smile:

they are tied to specific column(s) of a specific table

you needn’t worry about index naming conventions as the index names must simply be unique

what i will often do, if the index is on a single column, is give the index a name of columnname_ix

the only time you will even need the name of an index is if you need to drop it, and at that point, i always just look up the index name first (by doing a SHOW CREATE TABLE on the table), so it doesn’t even have to have a name that i assigned

r937 to my DB rescue again!

So by creating a Foreign Key (in MySQL), you don’t create an associated Index with it automatically??

(I guess maybe I should have posted this in the MySQL Forum?!)

So in order to have a Foreign Key (in MySQL) you have to define a Foreign Key Constraint??

Again, I’m used to using a CASE Tool and just establishing a “Relationship” between a Parent table and a Child table via a Primary Key and a Foreign Key and everything else being done for me?! :-/

So, “in the real world”, you have to define both a Foreign Key Constraint and a Foreign Key Index to establish the relationship?? :-/

TomTees

There is no need to create an index, but if MySQL (or any other DBMS for that matter) didn’t do that it would slow things down considerably. In order to know why we’d need to get to the physical level (hard disk I/O etc) of database tables and I don’t want to go there.

Suffice it to say an FK is a constraint for which MySQL creates an index to speed things along.

I’m pretty sure that’s Workbench there.

The key is explicit, the index is implicit.

r937 said it before: every key has an index, but not index every has to be related to a key (an apple is a fruit, but not every fruit is an apple). If you have a table with a column price that is not a foreign key you can still add an index to it in order to speed up queries with WHERE price=<somevalue> and/or ORDER BY price
(simplified example, that the price field has an index doesn’t necessarily mean MySQL will use it, depends on other factors of your query as well, but let’s not get into that – that deserves a thread (or book) on it’s own).

What happened when you tried? :wink:

Indexes are -in a slight abuse of notation- part of a table (actually they are internal lookup R-TREE or B-TREE storages that are related to the table). Constraints indicate relations between tables, defined on the table that references another one, so I suppose you could view those as belonging to the referencing table. Of course if the master table doesn’t exist the foreign key can’t exist also. I’m not sure if that would make them part of the database per se.

This post over at StackOverflow sorta sums up how I was initially feeling…

This is probably the most confusing topìc in MySQL.

Many people say that, for instance, the ‘PRIMARY KEY’, the ‘FOREIGN KEY’, and the ‘UNIQUE’ key are actually indexes! (MySQL official documentation is included here)

Many others, on the other hand, say that they’re rather constraints (which does make sense, cause when you use them, you’re really imposing restrictions on the affected columns).

If they really are indexes, then what’s the point in using the constraint clause in order to give it a name, since you’re supposed to be able to use the name of that index when you created it?

Example:

… FOREIGN KEY index_name (col_name1, col_name2, …)

If FOREIGN KEY is an index, then we should be able to use the index_name to handle it. However, we can’t.

But if they’re not indexes but actual constraints which do use indexes to work, then this does make sense.

In any case, we don’t know. Actually, nobody seems to know.
link|flag

answered Jul 11 at 23:00
Johann

There seems to be A LOT of misinformation floating around in “Database Land” about what a Foreign Key is and is not!!

My new understanding is that a “Foreign Key (FK)” is a type of Constraint. However, because fields that have this constraint on them are also used to link tables together in a Relational DB, there is also a need to Index said field(s) to help performance.

Does that sound right??

TomTees

Maybe Workbench is doing this, but all of my Foreign Keys are being named…

fk_<child_table>_<parent_table>

an index is an index, so when you create an index on a column, you’re doing it explicitly, but if you declare a foreign key, you’re creating it implicitly – there is no “override” concept here

I don’t follow you?!

Below is sample code for declaring a Foreign Key…


CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE

How is that implicit?

According to the manual - I’m getting there! - here is the syntax to create a table with a foreign key…


[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

What I was talking about is that if you don’t want MySQL to give your Foreign Key a name, you could type in your own name in the [symbol] area above, right??

again, da manual:[indent]InnoDB needs indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, these indexes are created automatically. In older versions, the indexes must be created explicitly or the creation of foreign key constraints fails.[/indent]

So are Indexes and Constraints part of the DATABASE or part of the specific TABLE??

TomTees

from da manual:[indent]In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you don’t assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, …) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. [/indent]

an index is an index, so when you create an index on a column, you’re doing it explicitly, but if you declare a foreign key, you’re creating it implicitly – there is no “override” concept here

again, da manual:[indent]InnoDB needs indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, these indexes are created automatically. In older versions, the indexes must be created explicitly or the creation of foreign key constraints fails.[/indent]

yes

once again, da manual:[indent]KEY [index_name] [index_type] (index_col_name,…)
|
INDEX [index_name] [index_type] (index_col_name,…)[/indent]the terms KEY and INDEX here are interchangeable

this is true in mysql, let’s not get into other databases, okay? :wink: