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.)

titleauteuryear
Black SwanAronofsky2010
The Girl With The Dragon TattooFincher2011
Atomic BlondeLeitch2017
Less Than ZeroKanievska1987
The Royal TenenbaumsAnderson2001

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' ; 
titleauteuryearrating
Black SwanAronofsky20101.2

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 AccessMySQL / Oracle < version 10gOracle > version 10g
ALTER TABLE tableALTER COLUMN column datatypeALTER TABLE tableMODIFY COLUMN column datatypeALTER 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' ;
titleauteuryearrating
Black SwanAronofsky20101

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

A 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' ;
titleauteuryearopinions
Black SwanAronofsky20101

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' ; 
titleauteuryear
Black SwanAronofsky2010

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

Start Learning

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 ) ;