ALTER TABLE to Change Columns: Data Characteristics

Beyond simply changing the data type, we may need to change other aspects of a table. One of the important items includes changing the size of a column. This can be important to optimize the size of a table or increase the size of a column to fit added data. The structure of the command will be the same as just changing the data type that we covered in the prior tutorial:

ALTER TABLE <tablename>
ALTER COLUMN <columnname>
TYPE <newdatatype>;

However, note that we can keep the same data type but just change the size of the data or use a smaller/larger version of the data type if one exists.

Let us take a look at a basic registration table used to capture individuals registering for an event:

CREATE TABLE registration(
registration_id int PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
email VARCHAR(30),
fee NUMERIC(4,2)
);

Let us add some sample data. We will cover this in a later tutorial:

INSERT INTO registration VALUES (1, 'Michelle','Pippen','mpippen@a.com',9.99);
INSERT INTO registration VALUES (2, 'Santana','Smith','smith@b.com',9.99);

No error should occur and if we query the table, we should see the two rows:

However, a new individual is trying to register with a last_name that had more than 10 characters that we had not expected. If we tried to insert a record in with a value longer than 10 for the last name like:

INSERT INTO registration VALUES (3, 'Joseph','Rudy-Potter','jpotter@c.com',9.99);

We should get an error like:

Although we can count up the number of characters and set the length of the variable to that value, we want to anticipate future potential sizes to avoid having to make this adjustment each time. It is better to error on the higher side rather than being too low. We can go ahead to change the length of the last name to 50 characters:

ALTER TABLE registration
ALTER COLUMN last_name
TYPE VARCHAR(50);

Now, if we run the insert statement again, we should have it run successfully:

We don’t have to only make the change to one column at a time if we need to make changes. For example, the first name and email probably should also have an increase in size. We can combine them together by listing each ALTER COLUMN statement separated by a comma:

ALTER TABLE registration
ALTER COLUMN first_name
TYPE VARCHAR(50),
ALTER COLUMN email
TYPE VARCHAR(100);

In looking at the fee, we are limited to 4 for precision with 2 characters as part of the scale. This means our maximum value that could be inserted in is 99.99. To change this, we can use the same statement to increase the precision of the fee to 6 digits:

ALTER TABLE registration
ALTER COLUMN fee
TYPE numeric(6,2);

This will allow values up to 9999.99 to be inserted into the table.

It is important to note that we cannot alter a column’s data type that has a foreign key reference to another table. Since there is a foreign key reference, the data type and size have to be the same as the primary key. For example, if we tried to alter the artist_id in the album table, due to the foreign key to the artist_id in the artist table, we would get the following error:

ALTER TABLE album
ALTER COLUMN artist_id
TYPE VARCHAR (100);

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next try your own choices for which columns you want the query to provide.