Altering a column to be nullable
SqlSql Problem Overview
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 Solutions
Solution 1 - Sql
Assuming SQL Server (based on your previous questions):
ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL
Replace INT with your actual datatype.
Solution 2 - Sql
If this was MySQL syntax, the type would have been missing, as some other responses point out. Correct MySQL syntax would have been:
ALTER TABLE Merchant_Pending_Functions MODIFY NumberOfLocations INT NULL
Posting here for clarity to MySQL users.
Solution 3 - Sql
In PostgresQL it is:
ALTER TABLE tableName ALTER COLUMN columnName DROP NOT NULL;
Solution 4 - Sql
for Oracle Database 10g users:
alter table mytable modify(mycolumn null);
You get "ORA-01735: invalid ALTER TABLE option" when you try otherwise
ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL;
Solution 5 - Sql
Although I don't know what RDBMS you are using, you probably need to give the whole column specification, not just say that you now want it to be nullable. For example, if it's currently INT NOT NULL, you should issue ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations INT.
Solution 6 - Sql
As others have observed, the precise syntax for the command varies across different flavours of DBMS. The syntax you use works in Oracle:
SQL> desc MACAddresses
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPUTER NUMBER
MACADDRESS VARCHAR2(12)
CORRECTED_MACADDRESS NOT NULL VARCHAR2(17)
SQL> alter table MACAddresses
2 modify corrected_MACAddress null
3 /
Table altered.
SQL> desc MACAddresses
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPUTER NUMBER
MACADDRESS VARCHAR2(12)
CORRECTED_MACADDRESS VARCHAR2(17)
SQL>
Solution 7 - Sql
This depends on what SQL Engine you are using, in Sybase your command works fine:
ALTER TABLE Merchant_Pending_Functions
Modify NumberOfLocations NULL;
Solution 8 - Sql
Oracle
> ALTER TABLE Merchant_Pending_Functions MODIFY([column] NOT NULL);
Solution 9 - Sql
For HSQLDB:
ALTER TABLE tableName ALTER COLUMN columnName SET NULL;
Solution 10 - Sql
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;
Solution 11 - Sql
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
Solution 12 - Sql
Make sure you add the data_type of the column to modify.
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE NULL;