本文翻译自:Altering a column to be nullable
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
. 这会在Modify
出错。 What is the correct syntax? 什么是正确的语法?
#1楼
参考:https://stackoom.com/question/eVjc/将列更改为可为空
#2楼
for Oracle Database 10g users: 对于Oracle数据库10g用户:
alter table mytable modify(mycolumn null);
You get "ORA-01735: invalid ALTER TABLE option" when you try otherwise 如果不这样做,会得到“ORA-01735:无效的ALTER TABLE选项”
ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL;
#3楼
If this was MySQL syntax, the type would have been missing, as some other responses point out. 如果这是MySQL语法,那么类型就会丢失,正如其他一些响应所指出的那样。 Correct MySQL syntax would have been: 正确的MySQL语法应该是:
ALTER TABLE Merchant_Pending_Functions MODIFY NumberOfLocations INT NULL
Posting here for clarity to MySQL users. 为了清楚MySQL用户,请在此处发布。
#4楼
Assuming SQL Server
(based on your previous questions): 假设SQL Server
(基于您之前的问题):
ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL
Replace INT
with your actual datatype. 将INT
替换为您的实际数据类型。
#5楼
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. 虽然我不知道您使用的是什么RDBMS,但您可能需要提供整个列规范,而不仅仅是说您现在希望它可以为空。 For example, if it's currently INT NOT NULL
, you should issue ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations INT
. 例如,如果它当前为INT NOT NULL
,则应发出ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations INT
。
#6楼
As others have observed, the precise syntax for the command varies across different flavours of DBMS. 正如其他人所观察到的,命令的精确语法因DBMS的不同风格而异。 The syntax you use works in Oracle: 您使用的语法适用于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>