#MYSQL ALTER TABLE ADD COLUMN HOW TO#

The foreign key in the comments table is the employeeid column. In the above command employeeid_comments_FK is used to create and INDEX for the employeeid column. Now we will add the foreign key constraint.ĪDD CONSTRAINT `employeeid_comments_FK` FOREIGN KEY (`employeeid`) In the above example the foreign key column detail `employeeid` VARCHAR(20) NOT NULL is same as the employeeid column detail of the employee table. The foreign key column detail must match the column detail of the table it is referring to. The employeeid will be the foreign key and it will refer to the employeeid column of the employee table.Īdding the new column employeeid after columnid.ĪDD COLUMN `employeeid` VARCHAR(20) NOT NULL Now, lets add a new column employeeid to the comments table after the commentid column. UNIQUE KEY `commentid_comments_UNIQUE` (`commentid`) `commentid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, In the CREATE Table tutorial we created the comments table which looks like the following at the moment. To add a foreign key to a table we use the ADD CONSTRAINT. In the following example we are dropping the firstname_employee_INDEX index from the firstname column. To drop or delete an index from a column we use the DROP INDEX command and the index symbol. ) ENGINE=INNODB DEFAULT CHARSET=UTF8 Drop index

KEY `firstname_employee_INDEX` (`firstname`) UNIQUE KEY `email_employee_UNIQUE` (`email`), So, our table will look like the following. In the following example we are adding an index to the firstname column.ĪDD INDEX `firstname_employee_INDEX` (`firstname`) 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.

| Field | Type | Null | Key | Default | Extra | In such situation you can use the ALTER TABLE statement to alter or change an existing table by adding, changing, or deleting a column in the table.Ĭonsider we've a shippers table in our database, whose structure is as follows: It is quite possible that after creating a table, as you start using it, you may discover you've forgot to mention any column or constraint or specified a wrong name for the column.

#MYSQL ALTER TABLE ADD COLUMN HOW TO#

In this tutorial you will learn how to alter or modify an existing table using SQL.

Mysql alter table add column