0

I have two tables: users and cities.

users               cities
------              ------
id(PK)              id(PK)
name                name
idCity(FK)

and I want to create that foreign key (idCity) properly. I am using phpmyadmin so I saw that I had to do:

  • Create an index on the table in which I want to create the foreign key, in this case, users, so I created an index called idCity that represents the column idCity.

  • I went to the structure section and related that index with the id of the table cities.

But I have some doubts in the first step: What index of the five provided should I choose?

After reading MySQL documentation in which I did not clarify my doubts very much, I searched on SO and found this question: Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL? that makes me to clarify a bit more my ideas but I still have some doubts about what I have to choose.

My thoughts of each index type

  • Primary: Of course I do not have to use it because what I want to achieve it is not a primary key, it is a foreign key.

  • Index: I could not get a very clear idea of what this index it is but what I could understood on the question of SO that I put above it is: the index may contain rows with identical values in all columns of the index.

    As my index represents only the column idCity and there could be a lot of users living in the same city, I think this could be the best option.

  • Unique: UNIQUE refers to an index where all rows of the index must be unique. This is the main reason why I have doubts between this one and INDEX option. My index only represents one column and I want that the values in that column could be repeated, so I am not secure if I have to use it or not.

  • FULLTEXT and SPATIAL: I have discarded these options because I saw that on phpmyadmin I have to create my tables as InnoDB type and these types of index are supported in other types of table.

My conclusion

I have doubts between UNIQUE and INDEX types of index. This is because I do not know if when someone refers to: UNIQUE refers to an index where all rows of the index must be unique, for example, when he is talking about all rows of the index, if it refers that all columns of the table do not have to be the same, that is what I would like to get (it can be more than one user living in a city but not two users repeated).

If it refers that all rows do not have to be the same I think the best approach have to be UNIQUE because I do not want that two rows will be the same but in the case that it only refers to the index itself (my idCity column) then I would have to choose INDEX type of index, because I want that more than one user could live in a city.

My questions

  • Am I creating my foreign key properly on phpmyadmin? I mean, Am I correctly following each step?

  • Which of my conclusions is correct? Or none of them?

Edit: The question that has been provided as possible duplicate answers to my first one question but not to the second one. Please read the full question before mark it as duplicate. Thanks!

Thanks in advance!

Community
  • 1
  • 1
Francisco Romero
  • 12,787
  • 22
  • 92
  • 167
  • @VikingBlooded It solves my first question but not the second one. – Francisco Romero Apr 15 '16 at 15:40
  • I always name my keys PK_for primary, FK_ for foreign. Those are the indexed keys. You should be using INDEX type for indexing these. – VikingBlooded Apr 15 '16 at 15:43
  • You can also look here: http://stackoverflow.com/questions/388982/mysql-5-0-indexes-unique-vs-non-unique – VikingBlooded Apr 15 '16 at 15:44
  • @VikingBlooded But to put `PK_` or `FK_` it is just to see them clearly, right? And could you explain a bit more extended why should I use `INDEX` on an answer? It is for the reason that I explained before? I read the second question that you provided and I think it is the reason, but I am not totally secure. Thanks! – Francisco Romero Apr 15 '16 at 15:57
  • Correct, the naming convention is totally for quick reference only. It's a hold over from another type of database application i used to develop in and it was a standard convention so I kept it. In terms of usage, UNIQUE is for primary keys, index can be used for any keys or columns to be indexed. – VikingBlooded Apr 15 '16 at 16:19
  • @VikingBlooded but for example if I have two lines that are the same. Are they allowed? – Francisco Romero Apr 15 '16 at 20:30
  • You should not have any duplicates with this setup since a user should exist only once and a city should exist only once. The only time you should see duplicates would be if you have a join table in the middle or you have duplicate entries for a user. Either way, only the data itself should be duplicated not the primary key for the user or the primary key for the city. – VikingBlooded Apr 18 '16 at 17:53
  • @VikingBlooded Yes, of course I do not want that primary keys should be duplicated (they won't because they are autoincrement). The question it is that, if for example I have a man with id 01 and a man with id 02, can they live in the same city with id 01, for example? It was the reason of my question, because I did not know if I should set index or unique values for that purpose. – Francisco Romero Apr 18 '16 at 18:35
  • Yes... that would be the point of the foreign key relationship. Your Primary keys are unique, your foreign keys can be indexed, but should not be unique in the child table containing the foreign key. – VikingBlooded Apr 18 '16 at 19:31
  • @VikingBlooded So I still do not understand what it is the difference between put index or unique, really. What is the main difference between them? That if I put unique two different users won't be able to live in the same city? – Francisco Romero Apr 18 '16 at 19:33
  • Unique belongs on your primary key. It means that each record in that table will have or has to have a unique identifier (Primary Key). Indexed means that MySQL/SQL or whatever else DB is used, will store an index of the keys told to be indexed for faster relation pulling and searching. If you put unique on the FOREIGN KEY, then yes, the db will throw an error at you when you try to put 2 people in the same city. – VikingBlooded Apr 18 '16 at 19:35
  • @VikingBlooded Ok, thank you. Now all it is more clear. This is the point that I was doubting all the time. – Francisco Romero Apr 18 '16 at 19:39

0 Answers0