如何在包含空值的列上添加非空约束 [英] How to add a not null constraint on column containing null values

问题描述

我有一个包含几个空值的列的表。我想在该列上添加 NOT NULL 约束，而不将现有的null更新为非空值。我想保留现有的空值，并检查未来的行，他们包含此列的非空值。这可能吗？如何？

I have a table with a column that contains a few null values. I want to add a NOT NULL constraint on that column without updating the existing nulls to a non-null value. I want to keep the existing null values and check for future rows that they contain a not null value for this column. Is this possible? How?

推荐答案

您可以添加未验证的约束 - 它不会查看现有行，或更新的行。

You can add an unvalidated constraint - it will not look at existing rows, but it will be checked for any new or updated rows.

ALTER TABLE mytable MODIFY mycolumn NOT NULL NOVALIDATE;

请注意，只有满足约束条件，您才能更新现有行。

Just be aware that you won't be able to update an existing row unless it satisfies the constraint.

另外，请注意优化器在制定计划时无法利用此约束的缺点 - 它必须假设某些行可能仍然有一个null。

Also, be aware of the downside that the optimizer will not be able to take advantage of this constraint in making its plans - it has to assume that some rows may still have a null.

这篇关于如何在包含空值的列上添加非空约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！