w3resource

MySQL Alter Table Statement Exercises: Drop the index indx_job_id from job_history table

MySQL Alter Table Statement: Exercise-15 with Solution

Write a MySQL statement to drop the index indx_job_id from job_history table.

Here is the structure of the job_history and index file of the table job_history.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID   | int(11) | NO   | PRI | NULL    |       |
| START_DATE    | date    | YES  |     | NULL    |       |
| HIRE_DATE     | date    | YES  |     | NULL    |       |
| JOB_ID        | int(11) | NO   | MUL | NULL    |       |
| DEPARTMENT_ID | int(11) | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+


mysql> SHOW INDEXES FROM job_history;
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| job_history |          0 | PRIMARY     |            1 | EMPLOYEE_ID | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| job_history |          1 | indx_job_id |            1 | JOB_ID      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+

Code:

 -- This SQL statement is used to alter the 'job_history' table by dropping (removing) an existing index.
-- The index named 'indx_job_id' is being removed.

ALTER TABLE job_history

-- Drop the index named 'indx_job_id' from the 'job_history' table.
DROP INDEX indx_job_id;

Let execute the above code in MySQL command prompt

Now see the structure of the table job_history after being altered.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID   | int(11) | NO   | PRI | NULL    |       |
| START_DATE    | date    | YES  |     | NULL    |       |
| HIRE_DATE     | date    | YES  |     | NULL    |       |
| JOB_ID        | int(11) | NO   |     | NULL    |       |
| DEPARTMENT_ID | int(11) | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

Now see the index file.

mysql> SHOW INDEXES FROM job_history;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| job_history |          0 | PRIMARY  |            1 | EMPLOYEE_ID | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Explanation:

Here's a brief explanation of the above MySQL code:

  • ALTER TABLE job_history: This part of the statement indicates that you want to make changes to the structure of the 'job_history' table.
  • DROP INDEX indx_job_id;: It drops (removes) the index named 'indx_job_id' from the 'job_history' table. The 'indx_job_id' is the name of the index created earlier.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to add an index named indx_job_id on job_id column in the table job_history.
Next: Basic SELECT statement

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/mysql-exercises/alter-table-statement/alter-table-exercise-15.php