470

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?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
challengeAccepted
  • 7,106
  • 20
  • 74
  • 105

13 Answers13

729

Assuming SQL Server (based on your previous questions):

ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL

Replace INT with your actual datatype.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 8
    Tried to use the table "Design" utility in SSMS to change a bit column from NOT NULL to NULL. I got an error that indicated that the table needed to be dropped and re-created. Whoa! This ALTER TABLE syntax worked and was much easier than dropping and re-creating a table to make a single column nullable. – JohnH Mar 12 '21 at 17:19
  • 3
    @JohnH Most attempts to modify a table with the Designer result in that warning. You can remedy that by changing the options in SSMS: https://stackoverflow.com/questions/1969096/saving-changes-after-table-edit-in-sql-server-management-studio – JLRishe Aug 19 '22 at 12:09
81

In PostgresQL it is:

ALTER TABLE tableName ALTER COLUMN columnName DROP NOT NULL;
Paul LeBeau
  • 97,474
  • 9
  • 154
  • 181
74

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.

djjeck
  • 1,781
  • 17
  • 25
49

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;
Igor S.
  • 3,332
  • 1
  • 25
  • 33
9

For SQL Server or TSQL

ALTER TABLE Complaint.HelplineReturn ALTER COLUMN IsDisposed BIT NULL
Luqman Cheema
  • 409
  • 6
  • 5
8

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.

Hammerite
  • 21,755
  • 6
  • 70
  • 91
  • 1
    This is a correct and descriptive answer, so just clarifying if `Null` | `NOT NULL` is not specified, the column will be nullable. – Hamid Heydarian Aug 11 '17 at 06:41
4

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>
APC
  • 144,005
  • 19
  • 170
  • 281
2

Oracle

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

Rodrigo Itursarry
  • 31
  • 3
  • Does this answer bring anything new with respect to @IgorS's [answer](https://stackoverflow.com/a/16664940/8516269)? And what does `SQL_SCRIPT` stand for? – jpeg May 29 '19 at 14:05
2

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

ALTER TABLE Merchant_Pending_Functions 
Modify NumberOfLocations NULL;
ZORRO_BLANCO
  • 849
  • 13
  • 25
1

For HSQLDB:

ALTER TABLE tableName ALTER COLUMN columnName SET NULL;
Libor B.
  • 519
  • 5
  • 9
1
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;
chamzz.dot
  • 607
  • 2
  • 12
  • 24
1

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
Martin Thoma
  • 124,992
  • 159
  • 614
  • 958
  • Thanks Dr Hipp. SQLite is unusual as the schema is stored character for character as the user entered it, including all the white space. This is claimed as an advantage as it takes less storage. But makes DDL tasks difficult. Also a real pain for DBAs. – Ben Feb 14 '22 at 16:57
  • What do you mean with "Dr Hipp"? – Martin Thoma Feb 14 '22 at 21:27
  • 3
    Dr Richard Hipp wrote SQLite. And made the world a better place. – Ben Feb 15 '22 at 22:19
-1

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

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE NULL;
Hybris95
  • 2,286
  • 2
  • 16
  • 33
abraham
  • 27
  • 6