Alter Drop Rename in sql
Donald Green
WHAT IS AN ALTERNA TEAM?
As they say, change is the only constant.
Business requirements change over time. As business requirements change, the database design must also change.
MySQL provides an ALTER function that helps us make changes to an already existing database design .
The alter command is used to modify an existing database, table, view, or other database object that may need to be modified during the database's life cycle.
Let's assume that we have completed the development of our database and it has been implemented. Our database users use it and then they realize that some important information was left out during the design phase. They don't want to lose existing data, they just want to include new information. The alter command comes in handy in such situations. We can use the alter command to change the data type of a field from, say, string to numeric, change the field name to a new name, or even add a new column to the table.
ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type`;
HERE
- "ALTER TABLE` table_name`" is a command that tells the MySQL server to change the table named "table_name".
- "ADD COLUMN` column_name` `data_type`" is a command that tells the MySQL server to add a new column named "column_name" with data type "data_type".
Let's assume that Myflix has introduced online billing and payments. To that end, we've been asked to add a credit card number field to our member table. We can use the ALTER command to do this. Let's first look at the members table structure before making any changes. The script shown below helps us to do this.
SHOW COLUMNS FROM `members`;
| field | type | Null | key | Default | Extra |
|---|---|---|---|---|---|
| membership_number | int(11) | NO | PRI | NULL | auto_increment |
| full_names | varchar(350) | NO | NULL | ||
| gender | varchar(6) | YES | NULL | ||
| date_of_birth | date | YES | NULL | ||
| physical_address | varchar(255) | YES | NULL | ||
| postal_address | varchar(255) | YES | NULL | ||
| contact_number | varchar(75) | YES | NULL | ||
| varchar(255) | YES | NULL |
We can use the script shown below to add a new field to the members table.
ALTER TABLE `members` ADD COLUMN `credit_card_number` VARCHAR(25);
Running the above script in MySQL against Myflixdb adds a new column named credit card number to the members table with VARCHAR as the data type. Running the show columns script gives us the following results.
SHOW COLUMNS FROM `members`;
| field | type | Null | key | Default | Extra |
|---|---|---|---|---|---|
| membership_number | int(11) | NO | PRI | NULL | auto_increment |
| full_names | varchar(350) | NO | NULL | ||
| gender | varchar(6) | YES | NULL | ||
| date_of_birth | date | YES | NULL | ||
| physical_address | varchar(255) | YES | NULL | ||
| postal_address | varchar(255) | YES | NULL | ||
| contact_number | varchar(75) | YES | NULL | ||
| varchar(255) | YES | NULL | |||
| credit_card_number | varchar(25) | YES |
As you can see from the results, the credit card number has been added to the participants table. The data contained in the member data is independent of adding a new column.
WHAT IS A DROP TEAM?
The DROP command is used to
- Remove database from MySQL server
- Delete an object (for example, Table, Column) from the database.
Let's now look at practical examples that use the DROP command.
In our previous example with the Alter command, we added a column named credit card number to the members table.
Suppose online billing will take some time and we want to remove the credit card column.
We can use the following script
ALTER TABLE `members` DROP COLUMN `credit_card_number`;
When executing the above script, the credit_card_number column is removed from the members table
Let's now look at the columns in the members table to confirm if our column has been dropped.
SHOW COLUMNS FROM `members`;
Running the above script in MySQL Workbench against myflixdb gives us the following results.
.
| field | type | Null | key | Default | Extra |
|---|---|---|---|---|---|
| membership_number | int(11) | NO | PRI | NULL | auto_increment |
| full_names | varchar(150) | NO | NULL | ||
| gender | varchar(6) | YES | NULL | ||
| date_of_birth | date | YES | NULL | ||
| physical_address | varchar(255) | YES | NULL | ||
| postal_address | varchar(255) | YES | NULL | ||
| contact_number | varchar(75) | YES | NULL | ||
| varchar(255) | YES | NULL |
Suppose we want
- Change the field name from "full_names" to "fullname
- Change it to datatype char with width 250
- Add a NOT NULL constraint
We can do this using the change command as follows:
ALTER TABLE `members` CHANGE COLUMN `full_names` `fullname` char(250) NOT NULL;
Running the above script in MySQL workbench for myflixdb and then running the show columns script above gives the following results.
| field | type | Null | key | Default | Extra |
|---|---|---|---|---|---|
| membership_number | int(11) | NO | PRI | NULL | auto_increment |
| fullnames | char(250) | NO | NULL | ||
| gender | varchar(6) | YES | NULL | ||
| date_of_birth | date | YES | NULL | ||
| physical_address | varchar(255) | YES | NULL | ||
| postal_address | varchar(255) | YES | NULL | ||
| contact_number | varchar(75) | YES | NULL | ||
| varchar(255) | YES | NULL |
MODIFY KEYWORD
The MODIFY keyword allows you to
- Change column data type
- Edit Column Constraints
In the CHANGE example above, we had to change the field name as well as other details. Omitting a field name from a CHANGE statement will result in an error. Let's assume that we are only interested in changing the data type and constraints on the field without affecting the field name. To do this, we can use the MODIFY keyword.
The script below changes the width of the "full name" field from 250 to 50.
ALTER TABLE `members`MODIFY `fullname` char(50) NOT NULL;
Running the above script in MySQL workbench for myflixdb and then running the show columns script above gives the following results shown below.
| field | type | Null | key | Default | Extra |
|---|---|---|---|---|---|
| membership_number | int(11) | NO | PRI | NULL | auto_increment |
| fullnames | char(50) | NO | NULL | ||
| gender | varchar(6) | YES | NULL | ||
| date_of_birth | date | YES | NULL | ||
| physical_address | varchar(255) | YES | NULL | ||
| postal_address | varchar(255) | YES | NULL | ||
| contact_number | varchar(75) | YES | NULL | ||
| varchar(255) | YES | NULL |
AFTER KEYWORD
Let's say we want to add a new column at a specific position in a table.
We can use the alter command along with the AFTER keyword.
The script below adds "date_of_registration" just after the date of birth in the members table.
ALTER TABLE `members` ADD `date_of_registration` date NULL AFTER `date_of_birth`;
Running the above script in MySQL workbench for myflixdb and then running the show columns script above gives the following results shown below.
| field | type | Null | key | Default | Extra |
|---|---|---|---|---|---|
| membership_number | int(11) | NO | PRI | NULL | auto_increment |
| fullnames | char(50) | NO | NULL | ||
| gender | varchar(6) | YES | NULL | ||
| date_of_birth | date | YES | NULL | ||
| date_of_registration | date | YES | NULL | ||
| physical_address | varchar(255) | YES | NULL | ||
| postal_address | varchar(255) | YES | NULL | ||
| contact_number | varchar(75) | YES | NULL | ||
| varchar(255) | YES | NULL |
Note: The Hilighted row is added after date_of_birth cloumn
Summary
- The alter command is used when we want to modify a database or any object contained in the database.
- The drop command is used to delete databases from MySQL server or objects within a database.
- The rename command is used to change the name of a table to a new table name.
- The Change keyword allows you to change a column name , data type and constraints
- The Modify Keyword allows you to modify a column data type and constraints
- The After keyword is used to specify the position of a column in a table
