We add index to columns to optimise search. To drop or delete a unique key constraint we use the DROP INDEX command.įor example, if we want to drop the UNIQUE KEY constraint from the email column by the symbol email_employee_UNIQUE we will run the following command. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Drop unique key UNIQUE KEY `email_employee_UNIQUE` (`email`) UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`), Now, our table will look like the following. In the following example we are adding a new column email to the employee table.Īnd now we are making it unique so that no two employee gets the same email address.ĪDD UNIQUE KEY `email_employee_UNIQUE` (`email`) We add the UNIQUE KEY constraint to ensure that the column contains distinct values. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Add unique key Now the table will look like the following. Now we will drop the temp column from the table. Right now the table looks like the following. In the following example we are dropping the temp column from the employee table. To drop or delete a column from a table we use the DROP COLUMN command. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Drop column Lets rename the dateofbirth column back to birthday and modify the column to NOT NULL and type from DATETIME to DATE.ĬHANGE COLUMN `dateofbirth` `birthday` DATE NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Rename and modify column Lets rename the birthday column to dateofbirth.ĬHANGE COLUMN `birthday` `dateofbirth` DATETIME DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Rename column MODIFY COLUMN `birthday` DATETIME DEFAULT NULL Lets modify the birthday column that we added to the employee table by changing its type from DATE to DATETIME. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Modify column Now our table will look like the following. Now we will add the birthday column after score column. UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`) In the following example we are adding birthday column to the employee table that we created in the CREATE Table tutorial. And in this tutorial we will cover some of the commonly used modification that we will encounter. We use the ALTER TABLE table_name command to alter tables in MySQL. In this tutorial we will learn to alter tables in MySQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |