Altering a column to be nullable

user_397062 0
Viewed 557932

I want to alter a table column to be nullable. I have used: 

ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations NULL

This gives an error at Modify. What is the correct syntax?

sql
13 Answers

This depends on what SQL Engine you are using, in Sybase your command works fine:

ALTER TABLE Merchant_Pending_Functions 
Modify NumberOfLocations NULL;
user_23654590

For HSQLDB:

ALTER TABLE tableName ALTER COLUMN columnName SET NULL;
user_40577880

Oracle

ALTER TABLE Merchant_Pending_Functions MODIFY([column] NOT NULL);

user_96383250

For SQL Server or TSQL

ALTER TABLE Complaint.HelplineReturn ALTER COLUMN IsDisposed BIT NULL
user_44117510 
ALTER TABLE Merchant_Pending_Functions MODIFY COLUMN `NumberOfLocations` INT null;

This will work for you.

If you want to change a not null column to allow null, no need to include not null clause. Because default columns get not null.

ALTER TABLE Merchant_Pending_Functions MODIFY COLUMN `NumberOfLocations` INT;
user_59831360

SQLite

The ALTER TABLE command is a bit special. There is no possibility to modify a column. You have to create a new column, migrate the data, and then drop the column:

-- 1. First rename
ALTER TABLE
    Merchant_Pending_Functions
RENAME COLUMN
    NumberOfLocations
TO
   NumberOfLocations_old

-- 2. Create new column
ALTER TABLE
    Merchant_Pending_Functions
ADD COLUMN
    NumberOfLocations INT NULL

-- 3. Migrate data - you need to write code for that
-- 4. Drop the old column
ALTER TABLE
    Merchant_Pending_Functions
DROP COLUMN
    NumberOfLocations_old
user_5627690

Make sure you add the data_type of the column to modify.

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE NULL;
user_62501390
Related