如何向 MySQL 中的现有列添加非空约束
我有一个名为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;
注意事项:在使用 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.
相关文章