FuzzDuck
FuzzDuck
Alter table
FuzzDuck
Alter table

Alter table

When setting up a database you might realize midway, that you have come up with the wrong database name. To change the name, you can call the ALTER DATABASE command, take the name of the old database, and give it a new name of your choice.

--- SQL command to rename database ---
ALTER DATABASE oldDatabaseName MODIFY NAME = newDatabaseName;

Add a column

Our table is missing a column, and we want it added alongside our existing table with its data. With the ALTER TABLE tableName ADD newColumnName newColumnType we can increase the size of our table. To add a column to our Ducks table, we call the command ALTER TABLE Ducks ADD address VARCHAR(100);. Now we created a column with the value address of type VARCHAR, which can contain 100 characters.

--- SQL command to add a column to a specific table ---
ALTER TABLE tableName ADD newColumnName newColumnType;

--- SQL command to add the column address to Ducks table ---
ALTER TABLE Ducks ADD address VARCHAR(100);

Delete a column

We decide that a column has become obsolete, and want it removed from the table. With the command, ALTER TABLE tableName DROP COLUMN columnName;, we can remove a specific column without touching the rest of the data. If we want our newly created column address from Ducks table to be removed we can simply call the command ALTER TABLE Ducks DROP COLUMN address;, and hence it is removed.

--- SQL command to remove a column from a specific table ---
ALTER TABLE tableName DROP COLUMN columnName;

--- SQL command to remove column address from Ducks table ---
ALTER TABLE Ducks DROP COLUMN address;

Change a column type

We find that our columns type is not fitting for the data it contains, so we want it changed. Again, we call for the ALTER TABLE. This time the command varies depending on whether we are using MS SQL or MYSQL. In MS SQL we use the command ALTER TABLE tableName ALTER COLUMN columnName newDataType;. In MYSQL, we use the command ALTER TABLE tableName MODIFY columnName newDataType;. In our Ducks example, we want the DataType changed from VARCHAR(100) to VARCHAR(150), so it has more room for the longer addresses. We call the command ALTER TABLE Ducks either followed by ALTER COLUMN address VARCHAR(150); or MODIFY address VARCHAR(150);.

--- MSSQL command to change column type and name ---
ALTER TABLE tableName ALTER COLUMN columnName newDataType;

--- MySQL command to change column type and name ---
ALTER TABLE tableName MODIFY columnName newDataType;

--- SQL command to remove column address from Ducks table ---
ALTER TABLE Ducks ALTER COLUMN address VARCHAR(150);
ALTER TABLE Ducks MODIFY address VARCHAR(150);