Altering a column to be nullable
Altering a column to be nullable
Question
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?
Accepted Answer
Assuming SQL Server
(based on your previous questions):
ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL
Replace INT
with your actual datatype.
Read more... Read less...
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.
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;
In PostgresQL it is:
ALTER TABLE tableName ALTER COLUMN columnName DROP NOT NULL;
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
.
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>