1


Today I have seen a video lecture in which they gave the foriegn key by using ADD INDEX on a table -

CASE 1 -

DECRIPTION OF TABLE 1 : subjects 

+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| id        | int(11)    | NO   | PRI | NULL    | auto_increment |
| menu_name | int(11)    | YES  |     | NULL    |                |
| position  | int(3)     | YES  |     | NULL    |                |
| visible   | tinyint(1) | YES  |     | NULL    |                |
+-----------+------------+------+-----+---------+----------------+

DECRIPTION OF TABLE 2 : pages 

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| subject_id | int(11)      | YES  |     | NULL    |                |
| menu_name  | varchar(255) | YES  |     | NULL    |                |
| position   | int(3)       | YES  |     | NULL    |                |
| visible    | tinyint(1)   | YES  |     | NULL    |                |
| content    | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

So in the column subject_id of table pages should store the id of table subjects.
Which one should i use and why ? -
ALTER TABLE pages ADD INDEX fk_subject_id (subject_id);
OR
ALTER TABLE pages ADD FOREIGN KEY (subject_id) REFERENCES students(id);
video lecture uses ALTER TABLE pages ADD INDEX fk_subject_id (subject_id);.

CASE 2 -

Now Please cosider one more example -
According to above details, If I have 5 more tables including pages table(defined above).
All 5 tables have column subject_id which should store the data accodring to column id of table subjects.
So in this case
In this case, Which one Should I use ADD INDEX or FOREIGN KEY and why ?

Abhishek kamal
  • 101
  • 1
  • 1
  • 9

2 Answers2

0

Q : case 1 - Which one should i use ?
A : I'll choose fk not index,because the reference between pages and subjects is multi to one,if you add index on column with duplicate values that would not be helpful,because in most cases, only one index can be used to optimize a database query,and there's a primary index on subjects,so don't do that again.

note : you have to make pages - subject_id not null

Q : case 2 - Which one should i use ?
A : if the 5 tables between subjects are also multi to one,i'll choose FK not index,the reason like case1's answer.

Wei Lin
  • 3,591
  • 2
  • 20
  • 52
0

Using a FOREIGN KEY and an INDEX are different things. FOREIGN KEYs are used for data integrity, so that you cannot have a reference, which points to nothing and that you cannot delete the "base" row without deleting the "linking" rows first (unless you use ON CASCADE DELETE stuff).

Indices are used to improve the search speed to find the correct rows faster in an SELECT and UPDATE query. This has nothing to do with data integrity.

To answer your question: You use a FOREIGN KEY if you want to reference the Id of rows from the other table (like you do with subject_id). Also, you don't need to add an INDEX on the column subject_id, because the InnoDB engine already does that.

Progman
  • 16,827
  • 6
  • 33
  • 48
  • ok thanks then also tell me how can i use both `ADD INDEX` and `FOREIGN KEY` to get fast search and data integrity respectively ? – Abhishek kamal Dec 08 '19 at 12:25
  • @Abhishekkamal As I already wrote it, you don't need to, the [InnoDB engine already does that](https://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically/304339#304339). – Progman Dec 08 '19 at 12:28
  • you got me wrong... I am not asking to add `INDEX` only on the column `subject_id`, it can be any column. – Abhishek kamal Dec 08 '19 at 12:38
  • @Abhishekkamal You can add an INDEX on almost any column type you want. See https://dev.mysql.com/doc/refman/8.0/en/create-index.html on how to add an index to one or more columns of your table. – Progman Dec 08 '19 at 12:40