CODEWITHSUNDEEP
mysqlphpmyadmin
Uncle Slug
Uncle Slug

Reputation: 913

Why are multiple indexes being generated for a table?

I've just realized that one of my tables, "pclass", has multiple instances of several foreign keys. In the Structure tab, #2-5 are the foreign keys. I have no idea why multiple instances are being generated.

Could they be generated by the JOINS? Please let me know if I need to provide other information. 

$brother_id = htmlspecialchars($_GET["brother_id"]);
$selected = $brother_id;
$query_brotherId = "SELECT b.id, b.firstname, b.lastname, b.pname, b.country, b.street01, b.street02, b.city, usStates.abv AS us_state, b.intl_state, b.postalcode, b.zipcode, b.phone, b.email, pclass.id AS pclass_id, greekAlphabet.name AS pclass01, prepclass.name AS prepclass, pclassSuffix.name AS pclass02, semester.name AS pclass_sem, pclass.year AS pclass_year, b.bigbrother_id AS bbID, bb.firstname AS bbFirst, bb.lastname AS bbLast, b.status, b.comments 
FROM brothers AS b 
LEFT JOIN pclass ON b.pclass_id = pclass.id
LEFT JOIN prepclass ON pclass.prepclass_id = prepclass.id
LEFT JOIN greekAlphabet ON pclass.greekAlphabet_id = greekAlphabet.id 
LEFT JOIN pclassSuffix ON pclass.suffix_id = pclassSuffix.id 
LEFT JOIN semester ON pclass.semester_id = semester.id 
LEFT JOIN usStates ON b.us_state = usStates.id 
LEFT JOIN brothers AS bb ON b.bigbrother_id = bb.id 
WHERE b.id = $brother_id";
$result_brotherId = mysqli_query($link, $query_brotherId);

enter image description here

enter image description here

Upvotes: 0

Views: 38

Answers (1)

Philipp
Philipp

Reputation: 2796

First your question:

Could they be generated by the JOINS?

No. Foreign Keys are generated by data definition statements like CREATE TABLE, ALTER TABLE and so on.

I have no idea why multiple instances are being generated.

The person who created the database must have thought they will be useful. Or if you created the database via some sql-tool (don't know) the tool created the foreign keys because it got told there is a relation between those fields.

Why it is probably not bad to have the keys:

Foreign Keys are created to display the relations between your different table.

Also they enforce a specific behaviour when you are doing actions which could disrupt the integrity of your data. You can change this behaviour in your last screenshot.

For each foreign key you can give a name which will be shown in error messages when you try to act against the constraing. And you can define how the foreign key acts if you change or delete the parent field.

For example

You have the following tables displaying which tool belongs to which person.

persons

  • personid
  • firstname
  • lastname
  • ...

tools

  • toolid
  • personid (foreign key to persons)
  • name
  • ....

So in the tools table you have a foreign key to the persons table, this field defines the owner of the tool.

Now let's define some use cases

Assumption: For some reason Peter is no longer able to wield any tools, so he no longer fits into the database.

What should happen to his tools? It depends what your database displays!

  1. your database displays anyone who ever owned a tool.

This means, even if the person actually doesn't even exist anymore, the data should still remain. You would actually enforce this behaviour otherwise, but it would work in our current case to show what the foreign key can do.

So the action we choose for ON DELETE is RESTICT. (It also is the default action)

Now let's try to call: DELETE FROM persons WHERE firstname = 'Peter'

Result: the foreign key constraint will prompt you an error message. There are relations which depend on this entry in the persons table.

  1. The database displays persons and some tools, tools don't have to have an owner

In this case we again want to delete the person Peter. His tools can remain in the database, instead of the personid they will get a null value into this field.

So we choose the action ON DELETE: SET NULL

This one is pretty straight forward. Important: the field with the foreign key must not have a NOT NULL constraint.

  1. The database displays the people and the tools in a building or something..

So if Peter and his tools leave the building, we don't care about them anymore.

The action for ON DELETE: CASCADE.

If you now enter the DELETE-statement, the foreign key will take care of deleting all the other entries (the tools) connected to Peter.

Upvotes: 1

Related Questions