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

User · Answer

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

User · Answer

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

User · Answer

This depends on what SQL Engine you are using  in Sybase your command works fine   ALTER TABLE Merchant Pending Functions  Modify NumberOfLocations NULL

User · Answer

For HSQLDB   ALTER TABLE tableName ALTER COLUMN columnName SET NULL

User · Answer

Oracle     ALTER TABLE Merchant Pending Functions MODIFY  column   NOT NULL

User · Answer

In PostgresQL it is   ALTER TABLE tableName ALTER COLUMN columnName DROP NOT NULL

User · Answer

As others have observed  the precise syntax for the command varies across different flavours of DBMS   The syntax you use works in Oracle   SQL gt  desc MACAddresses  Name                                      Null     Type  ----------------------------------------- -------- ----------------------------  COMPUTER                                           NUMBER  MACADDRESS                                         VARCHAR2 12   CORRECTED MACADDRESS                      NOT NULL VARCHAR2 17   SQL gt  alter table MACAddresses   2       modify corrected MACAddress null   3     Table altered   SQL gt  desc MACAddresses  Name                                      Null     Type  ----------------------------------------- -------- ----------------------------  COMPUTER                                           NUMBER  MACADDRESS                                         VARCHAR2 12   CORRECTED MACADDRESS                               VARCHAR2 17   SQL gt

User · Answer

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

User · Answer

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 · 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

[sql] Altering a column to be nullable

Examples related to sql