如何向 MySQL 中的现有列添加非空约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6305225/
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
How to add not null constraint to existing column in MySQL
提问by mymotherland
I have table name called "Person" with following column names
我有一个名为“Person”的表名，其列名如下
P_Id(int),
LastName(varchar),
FirstName (varchar).
I forgot to give NOT NULLConstraint to P_Id.
我忘了给NOT NULL约束P_Id。
Now I tried with following query to add NOT NULLConstraint to existing column called P_Id,
现在我尝试使用以下查询将NOT NULL约束添加到名为 的现有列P_Id，
1. ALTER TABLE Person MODIFY (P_Id NOT NULL);
2. ALTER TABLE Person ADD CONSTRAINT NOT NULL NOT NULL (P_Id);
I am getting syntax error....
我收到语法错误....
回答by Shakti Singh
Just use an ALTER TABLE... MODIFY...query and add NOT NULLinto your existing column definition. For example:
只需使用ALTER TABLE... MODIFY...查询并添加NOT NULL到您现有的列定义中即可。例如：
ALTER TABLE Person MODIFY P_Id INT(11) NOT NULL;
A word of caution: you need to specify the fullcolumn definition again when using a MODIFYquery. If your column has, for example, a DEFAULTvalue, or a column comment, you need to specify it in the MODIFYstatement along with the data type and the NOT NULL, or it will be lost. The safest practice to guard against such mishaps is to copy the column definition from the output of a SHOW CREATE TABLE YourTablequery, modify it to include the NOT NULLconstraint, and paste it into your ALTER TABLE... MODIFY...query.
请注意：使用查询时需要再次指定完整的列定义MODIFY。例如，如果您的列具有DEFAULT值或列注释，则需要在MODIFY语句中指定它以及数据类型和NOT NULL，否则它将丢失。防止此类事故的最安全做法是从SHOW CREATE TABLE YourTable查询的输出中复制列定义，修改它以包含NOT NULL约束，然后将其粘贴到您的ALTER TABLE... MODIFY...查询中。
回答by Maran Manisekar
Try this, you will know the difference between change and modify,
试试这个，你就会知道改变和修改的区别，
ALTER TABLE table_name CHANGE curr_column_name new_column_name new_column_datatype [constraints]
ALTER TABLE table_name MODIFY column_name new_column_datatype [constraints]
- You can change name and datatype of the particular column using
CHANGE. - You can modify the particular column datatype using
MODIFY. You cannot change the name of the column using this statement.
- 您可以使用 更改特定列的名称和数据类型
CHANGE。 - 您可以使用 修改特定的列数据类型
MODIFY。您不能使用此语句更改列的名称。
Hope, I explained well in detail.
希望，我解释得很好。
