问题描述
我有一个名为"Person"的表名,并具有以下列名
I have table name called "Person" with following column names
P_Id(int),
LastName(varchar),
FirstName (varchar).
我忘了给P_Id
赋予NOT NULL
约束.
现在,我尝试使用以下查询将NOT NULL
约束添加到名为P_Id
的现有列中,
Now I tried with following query to add NOT NULL
Constraint to existing column called 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....
推荐答案
只需使用 ALTER TABLE... MODIFY...
查询,然后将NOT NULL
添加到您现有的列定义中.例如:
Just use an ALTER TABLE... MODIFY...
query and add NOT NULL
into your existing column definition. For example:
ALTER TABLE Person MODIFY P_Id INT(11) NOT NULL;
请注意:使用MODIFY
查询时,您需要再次指定 full 列定义.例如,如果您的列具有DEFAULT
值或列注释,则需要在MODIFY
语句中指定它以及数据类型和NOT NULL
,否则它将丢失.防止此类意外的最安全的做法是从SHOW CREATE TABLE YourTable
查询的输出中复制列定义,对其进行修改以包含NOT NULL
约束,然后将其粘贴到您的ALTER TABLE... MODIFY...
查询中.
A word of caution: you need to specify the full column definition again when using a MODIFY
query. If your column has, for example, a DEFAULT
value, or a column comment, you need to specify it in the MODIFY
statement 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 YourTable
query, modify it to include the NOT NULL
constraint, and paste it into your ALTER TABLE... MODIFY...
query.
这篇关于如何在MySQL中向现有列添加非空约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!