如何在Oracle 11g的一行中添加非空列和检查约束? [英] How do I add a not null column and a check constraint in one line in Oracle 11g?

问题描述

我正在尝试向表中添加新的INTEGER列.该列必须为NOT NULL，默认值为1，并且只能接受大于零的值.

I'm trying to add a new INTEGER column to a table. The column must be NOT NULL, with a default value of 1, and only accept values greater than zero.

此刻我正在尝试这样做:

I'm trying to do this at the moment:

ALTER TABLE FOO_AUTHORS 
ADD PUBLICATION_PERIOD_DAYS INTEGER DEFAULT(1) NOT NULL
CONSTRAINT publicationPeriodDays 
CHECK (PUBLICATION_PERIOD_DAYS>0);

有没有一种方法可以做到这一点?我正在跟踪此示例，但是由于NOT NULL，它无法正常工作.那么是否需要NOT NULL?

Is there a way to do this in one line? I'm following this example, but it's not working because of the NOT NULL. Is the NOT NULL then necessary?

我从数据库中收到以下错误:

I'm getting the following error from the DB:

QL错误:ORA-02293:无法验证(DBOWNER.PUBLICATIONPERIODDAYS)-违反了检查约束 02293. 00000-无法验证(％s.％s)-违反了检查约束" *原因:alter table操作试图验证检查约束以 没有合规值的填充表.

QL Error: ORA-02293: cannot validate (DBOWNER.PUBLICATIONPERIODDAYS) - check constraint violated 02293. 00000 - "cannot validate (%s.%s) - check constraint violated" *Cause: an alter table operation tried to validate a check constraint to populated table that had nocomplying values.

如果我在没有NOT NULL的情况下尝试使用它，则效果很好.

If I try it without the NOT NULL, it works fine.

推荐答案

将NOT NULL约束滚动到CHECK约束中:

ALTER TABLE FOO_AUTHORS 
  ADD PUBLICATION_PERIOD_DAYS INTEGER DEFAULT 1
  CONSTRAINT publicationPeriodDays
  CHECK ( PUBLICATION_PERIOD_DAYS IS NOT NULL AND PUBLICATION_PERIOD_DAYS > 0 );

现有行的PUBLICATION_PERIOD_DAYS设置为默认值.

The existing rows will have their PUBLICATION_PERIOD_DAYS set to the default value.

这篇关于如何在Oracle 11g的一行中添加非空列和检查约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！