Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Create MySQL column with Key=MUL?
You need to use ADD KEY to make a column with Key=MUL. The syntax is as follows −
ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type, ADD KEY(yourColumnName);
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table Instructor -> ( -> Instructor_Id int, -> Instructor_Name varchar(30), -> Instructor_CourseName varchar(100) -> ); Query OK, 0 rows affected (0.63 sec)
Now you can look the table description of the table, the column KEY does not have any MUL key. The query is as follows to check the description of table using DESC command.
mysql> desc Instructor;
The following is the output −
+-----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | Instructor_Id | int(11) | YES | | NULL | | | Instructor_Name | varchar(30) | YES | | NULL | | | Instructor_CourseName | varchar(100) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Here is the query to create a column in MySQL with Key=MUL. Make the column ‘Instructor_Id’ as Key=MUL. The query is as follows −
mysql> alter table Instructor modify column Instructor_Id int NOT NULL AUTO_INCREMENT, -> add key(Instructor_Id); Query OK, 0 rows affected (2.88 sec) Records: 0 Duplicates: 0 Warnings: 0
Now check the table description once again. The query is as follows −
mysql> desc Instructor;
The following is the output −
+-----------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+----------------+ | Instructor_Id | int(11) | NO | MUL | NULL | auto_increment | | Instructor_Name | varchar(30) | YES | | NULL | | | Instructor_CourseName | varchar(100) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
To add a non-primary key, you need to use the following query −
mysql> alter table Instructor modify column Instructor_Name varchar(30) NOT NULL, -> add key(Instructor_Name); Query OK, 0 rows affected (2.77 sec) Records: 0 Duplicates: 0 Warnings: 0
Now check the table description once again. The query is as follows −
mysql> desc Instructor;
The following is the output displaying Key as MUL for one of the fields −
+-----------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+----------------+ | Instructor_Id | int(11) | NO | MUL | NULL | auto_increment | | Instructor_Name | varchar(30) | NO | MUL | NULL | | | Instructor_CourseName | varchar(100) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
4K+ Views
