如何在MySQL5.1中向现有列添加非空约束 [英] How to add not null constraint to existing column in MySQL5.1
问题描述
我有名为Person的表名,包含以下列名称
I have table name called "Person" with following column names
P_Id(int),
LastName(varchar),
FirstName (varchar).
我忘记给予 NOT NULL
P_Id
。
现在我尝试使用以下查询添加 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;
注意:您需要再次指定 full 当使用 MODIFY
查询时。如果您的列具有例如 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.
这篇关于如何在MySQL5.1中向现有列添加非空约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!