SQL - Alter

SQL ALTER is the command used to add, edit, and modify data objects like tables, databases, and views. ALTER is the command responsible for making table column adjustments or renaming table columns. New table columns can also be added and dropped from existing SQL tables.

SQL Add:

USE mydatabase;

ALTER TABLE orders
ADD discount VARCHAR(10);

SQL Results:

id	customer	day_of_order	product	quantity	discount
1	Tia	2008-08-01 00:00:00.000	Pen	8	NULL
2	Tia	2008-08-01 00:00:00.000	Stapler	3	NULL
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	NULL
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	NULL
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	5	NULL
6	Tia	2008-07-25 00:00:00.000	HP Printer	4	NULL

As you can see from the results panel, SQL has added an additional column, discount, to the orders table. Since this column was just created, it contains no data, and only NULL values have been returned.

SQL - Alter Table: Modify Column

SQL table columns can be altered and changed using the MODIFY COLUMN command. This allows the developer the opportunity to mold table columns or adjust settings as needed.

SQL Modify Column:

USE mydatabase;

ALTER TABLE orders
ALTER COLUMN discount DECIMAL(18,2);

Above, we have modified the new discount table column changing the column data type from a varchar to a decimal table column. This example can be expanded to modify table columns as needed by the developer.

SQL - SQL Alter Table: Drop

This column can be deleted using the SQL DROP command. Once this column has been dropped, however, the data stored inside of it will be lost forever. Proceed with caution!

SQL Drop Column Code:

USE mydatabase;

ALTER TABLE orders
DROP COLUMN discount;

Posted in: SQL