SQL 修改表：如何将“允许空”属性从非空更改为允许空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3889916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Modify table: How to change 'Allow Nulls' attribute from not null to allow null
提问by CrazyMouse
How to change one attribute in a table using T-SQL to allow nulls (not null --> null)? Alter table maybe?
如何使用 T-SQL 更改表中的一个属性以允许空值（非空值 --> 空值）？可能改变表？
回答by LukeH
-- replace NVARCHAR(42) with the actual type of your column
ALTER TABLE your_table
ALTER COLUMN your_column NVARCHAR(42) NULL
回答by Daniel Vassallo
Yes you can use ALTER TABLEas follows:
是的，您可以ALTER TABLE按如下方式使用：
ALTER TABLE [table name] ALTER COLUMN [column name] [data type] NULL
Quoting from the ALTER TABLEdocumentation:
从ALTER TABLE文档中引用：
NULLcan be specified inALTER COLUMNto force aNOT NULLcolumn to allow null values, except for columns in PRIMARY KEY constraints.
NULL可以指定 inALTER COLUMN以强制NOT NULL列允许空值，但 PRIMARY KEY 约束中的列除外。
回答by Oded
ALTER TABLEis right:
ALTER TABLE是对的：
ALTER TABLE MyCustomers ALTER COLUMN CompanyName VARCHAR(20) NULL
回答by Vijay Nandwana
For MySQL, MariaDB
对于 MySQL、MariaDB
ALTER TABLE [table name] MODIFY COLUMN [column name] [data type] NULL
Use MODIFY COLUMNinstead of ALTER COLUMN.
使用MODIFY COLUMN代替ALTER COLUMN。
回答by laxmi kalake
ALTER TABLE public.contract_termination_requests
ALTER COLUMN management_company_id DROP NOT NULL;
回答by Jeffrey Pallatt
I wrote this so I could edit all tables and columns to null at once:
我写了这个，所以我可以一次将所有表和列编辑为空：
select
case
when sc.max_length = '-1' and st.name in ('char','decimal','nvarchar','varchar')
then
'alter table [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(MAX) NULL'
when st.name in ('char','decimal','nvarchar','varchar')
then
'alter table [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(' + cast(sc.max_length as varchar(4)) + ') NULL'
else
'alter table [' + so.name + '] alter column [' + sc.name + '] ' + st.name + ' NULL'
end as query
from sys.columns sc
inner join sys.types st on st.system_type_id = sc.system_type_id
inner join sys.objects so on so.object_id = sc.object_id
where so.type = 'U'
and st.name <> 'timestamp'
order by st.name
回答by Tilak Dewangan
This is the approach to do this: -
这是这样做的方法： -
- Check whether the table or column exists or not.
- If yes, then alter the column. e.g:-
- 检查表或列是否存在。
- 如果是，则更改列。例如：-
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_CATALOG = 'DBName' AND
TABLE_SCHEMA = 'SchemaName' AND
TABLE_NAME = 'TableName' AND
COLUMN_NAME = 'ColumnName')
BEGIN
ALTER TABLE DBName.SchemaName.TableName ALTER COLUMN ColumnName [data type] NULL
END
If you don't have any schema then deletethe schema line because you don't need to give the default schema.
如果您没有任何架构，则删除架构行，因为您不需要提供默认架构。
回答by Mohsin Younas
So the simplest way is,
所以最简单的方法是，
alter table table_name change column_name column_name int(11) NULL;
