Create MySQL column with Key=MUL?

MySQLMySQLi Database

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)
raja
Published on 26-Feb-2019 11:39:21
Advertisements