SQL Alter modifies existing columns and constraints in relational database tables.
A well-designed set of tables is the foundation of any database application. As time passes, however, initial assumptions are refined, additional scope is added to the project, and new understanding of the problem domain necessitate changing the table schema. Rather than deleting live production tables with a data migration process, the SQL Alter statement modifies the existing column asd constraints; generally a less invasive adjustment.
SQL Alter can change tables by adding and deleting columns, converting between (casting) data types, deleting (dropping) tables, and — for some database — renaming tables. The syntax of SQL Alter varies between commercial database vendors; check documentation specific to your type and version of database. Significant syntax variations are noted below.
The Starting Point
We start with a table of movies, their creators, and the release year. (The SQL to create and populate this table appears at the end of this post.)
|The Girl With The Dragon Tattoo||Fincher||2011|
|Less Than Zero||Kanievska||1987|
|The Royal Tenenbaums||Anderson||2001|
SQL Alter Add
ALTER TABLE TableName ADD ( ColumnOne datatype, ColumnTwo datatype, ColumnThree datatype ) ;
To add a column which holds the IMDB rating — a floating-point value — we can use the following:
ALTER TABLE movies ADD rating FLOAT ;
Table cells of added columns are NULL. To set a value in the new column, and then to examine it, use something like the following:
UPDATE movies SET rating = 1.2 WHERE title = 'Black Swan'; SELECT * FROM movies WHERE title = 'Black Swan' ;
SQL Alter Modify
SQL Alter Modify changes the column’s data type. There are three commonly-used syntax variants; check your database documentation for more details.
|SQL Server / Microsoft Access||MySQL / Oracle < version 10g||Oracle > version 10g|
|ALTER TABLE tableALTER COLUMN column datatype||ALTER TABLE tableMODIFY COLUMN column datatype||ALTER TABLE tableMODIFY column datatype|
Data Loss Issues When Converting Data Types
Altering column data types obviously modifies existing data; planning must be done to ensure no unexpected data loss. For example, changing the rating column from a FLOAT to an INT drops the fractional part.
ALTER TABLE movies MODIFY COLUMN rating INT ; SELECT * FROM movies WHERE title = 'Black Swan' ;
Not all data types can be converted between. A column containing character strings containing only numbers can be converted to numeric types but a string containing an alphabetic character will result in a conversion error.
Columns can be modified to make them “wider” — a
VARCHAR(10) can become a
VARCHAR(20) without issue but when a column is made “narrower” most databases will validate existing data against the proposed new data type and throw a truncation conversion error.
Adding NOT NULL Constraint
NOT NULL constraint may be added to a column provided no NULLs currently exists in the column. This constraint enforces a field to always contain a value, which means that you cannot insert a new record, or update a record, without including a value for this field.
ALTER TABLE movies MODIFY COLUMN rating INT NOT NULL ;
Conversion to the NULL equivalent — a zero or empty string — must be performed before adding a NOT NULL constraint.
UPDATE table SET column = '' WHERE column IS NULL ;
SQL Alter Rename
Some databases allow columns and tables to be renamed with the Alter Rename command. The following example first renaming the table from “movies” to “chill” and then renaming the “ratings” column to “opinions,” resulting in:
ALTER TABLE movies RENAME TO chill ; ALTER TABLE movies RENAME COLUMN ratings TO opinions ; SELECT * FROM chill WHERE title = 'Black Swan' ;
Some usage notes for those databases which support Alter Rename:
- Database objects — indexes and triggers — associated with the table being renamed will automatically be associated with the new table.
- Alter Rename affects tables within the currently active database. It cannot be used to move between databases; i.e. rename database_one.table to database_two.table will not work.
- Table names referenced within views or by trigger statements must have those manually renamed.
SQL Alter Drop
SQL Alter Drop drops (or deletes) table columns. Note please that some databases disallow dropping columns.
ALTER TABLE movies DROP COLUMN rating ; SELECT * FROM movies WHERE title = 'Black Swan' ;
SQL Alter Sample Table Code
The following will build and populate the sample data referred to in this blog post, data you can test interactively at SQL Fiddle.
CREATE TABLE movies ( title VARCHAR(50), auteur VARCHAR(33), year INT ) ; INSERT INTO movies VALUES ( "Black Swan", "Aronofsky", 2010 ), ( "The Girl With The Dragon Tattoo", "Fincher", 2011 ), ( "Atomic Blonde", "Leitch", 2017 ), ( "Less Than Zero", "Kanievska", 1987 ), ( "The Royal Tenenbaums", "Anderson", 2001 ) ;