How to Modify a Column in SQL

What you’ll build or solve

You’ll update the definition of an existing column in a table.

When this approach works best

Modifying a column works best when you:

• Increase the length of a text field
• Change a numeric type for better precision
• Add or remove constraints such as NOT NULL

For example, you might expand a VARCHAR(50) to VARCHAR(150), change an INT to BIGINT, or enforce that a column cannot be NULL.

This is a bad idea if existing data conflicts with the new definition. For example, shrinking a column length can cause errors or data loss.

Prerequisites

• Access to the database
• Permission to alter tables
• Basic SQL knowledge
• A backup if working in production

Step-by-step instructions

Step 1: Modify the column definition

Use ALTER TABLE with the correct syntax for your database.

MySQL

Use MODIFY or CHANGE:

ALTERTABLE users
MODIFY emailVARCHAR(150);

If you rename and modify at the same time:

ALTERTABLE users
CHANGE email user_emailVARCHAR(150);

PostgreSQL

Use ALTER COLUMN:

ALTERTABLE users
ALTERCOLUMN email TYPEVARCHAR(150);

To add or remove NOT NULL:

ALTERTABLE users
ALTERCOLUMN emailSETNOTNULL;

SQL Server

Use ALTER COLUMN:

ALTERTABLE users
ALTERCOLUMN email NVARCHAR(150);

All systems require the full new column definition when changing the type.

What to look for

• The new type must be compatible with existing data
• Shrinking column length can fail if data exceeds the new limit
• Changing constraints may fail if current data violates them
• Some systems require dropping constraints before modifying the type

Examples you can copy

Example 1: Increase text length

You need longer usernames.

MySQL

ALTERTABLE users
MODIFY usernameVARCHAR(150);

PostgreSQL

ALTERTABLE users
ALTERCOLUMN username TYPEVARCHAR(150);

Example 2: Change numeric precision

You want more decimal precision for prices.

MySQL

ALTERTABLE products
MODIFY priceDECIMAL(12,4);

PostgreSQL

ALTERTABLE products
ALTERCOLUMN price TYPEDECIMAL(12,4);

Example 3: Enforce NOT NULL

You decide that every order must have a status.

PostgreSQL

ALTERTABLE orders
ALTERCOLUMN statusSETNOTNULL;

SQL Server

ALTERTABLE orders
ALTERCOLUMN status NVARCHAR(50)NOTNULL;

Make sure existing rows already contain valid values before running this command.

Common mistakes and how to fix them

Mistake 1: Shrinking a column with incompatible data

What you might do:

ALTERTABLE users
MODIFY usernameVARCHAR(10);

Why it fails: If existing usernames are longer than 10 characters, the database blocks the change.

Correct approach: Check current data length first:

SELECT MAX(LENGTH(username))FROM users;

Then choose a safe size.

Mistake 2: Adding NOT NULL when NULL values exist

What you might do:

ALTERTABLE users
ALTERCOLUMN emailSETNOTNULL;

Why it fails: If any row contains NULL, the change is rejected.

Correct approach: Update rows first:

UPDATE users
SET email='unknown@example.com'
WHERE emailISNULL;

Then apply the constraint.

Mistake 3: Forgetting to restate the full definition in SQL Server

What you might do:

ALTERTABLE users
ALTERCOLUMN email;

Why it fails: SQL Server requires the complete data type definition.

Correct approach:

ALTERTABLE users
ALTERCOLUMN email NVARCHAR(150);

Always specify the full type.

Troubleshooting

• If you see “permission denied,” confirm you have ALTER privileges.
• If you see a type conversion error, verify existing data matches the new type.
• If the change fails due to constraints, review current values and update them before retrying.
• If indexes depend on the column, consider dropping and recreating them after modification.

Quick recap

• Use ALTER TABLE to modify a column
• Syntax differs across MySQL, PostgreSQL, and SQL Server
• Always specify the new data type fully
• Check existing data before shrinking size or adding constraints
• Update rows before enforcing NOT NULL
• Test structural changes before production