如何在MySQL中向现有列添加非空约束 [英] How to add not null constraint to existing column in MySQL

问题描述

我有一个名为"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中向现有列添加非空约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！