在检查约束中使用日期，Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5332562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using date in a check constraint, Oracle
提问by Jon
I am trying to check add the following constraint but Oracle returns the error shown below.
我正在尝试检查添加以下约束，但 Oracle 返回如下所示的错误。
ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > SYSDATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= SYSDATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (SYSDATE + 730))));
Error:
错误：
Error report:
SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 - "date or system variable wrongly specified in CHECK constraint"
*Cause: An attempt was made to use a date constant or system variable,
such as USER, in a check constraint that was not completely
specified in a CREATE TABLE or ALTER TABLE statement. For
example, a date was specified without the century.
*Action: Completely specify the date constant or system variable.
Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
which a bug permitted to be created before version 8.
回答by Justin Cave
A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs, i.e.
遗憾的是，检查约束不能引用像 SYSDATE 这样的函数。您需要创建一个触发器来在 DML 发生时检查这些值，即
CREATE OR REPLACE TRIGGER trg_check_dates
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
BEGIN
IF( :new.CloseDate <= SYSDATE )
THEN
RAISE_APPLICATION_ERROR( -20001,
'Invalid CloseDate: CloseDate must be greater than the current date - value = ' ||
to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
END IF;
IF( :new.CloseDate > add_months(SYSDATE,12) )
THEN
RAISE_APPLICATION_ERROR( -20002,
'Invalid CloseDate: CloseDate must be within the next year - value = ' ||
to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
END IF;
IF( :new.StartDate <= add_months(:new.CloseDate,24) )
THEN
RAISE_APPLICATION_ERROR( -20002,
'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' ||
to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) ||
' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) );
END IF;
END;
回答by Marcin Wroblewski
You cannot use SYSDATE in check constraint. According to documentation
您不能在检查约束中使用 SYSDATE。根据文档
Conditions of check constraints cannot contain the following constructs:
- Subqueries and scalar subquery expressions
- Calls to the functions that are not deterministic(CURRENT_DATE,
CURRENT_TIMESTAMP, DBTIMEZONE,
LOCALTIMESTAMP, SESSIONTIMEZONE,
SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)- Calls to user-defined functions
- Dereferencing of REF columns (for example, using the DEREF function)
- Nested table columns or attributes
- The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
- Date constants that are not fully specified
检查约束条件不能包含以下结构：
- 子查询和标量子查询表达式
- 调用不确定的函数（CURRENT_DATE、
CURRENT_TIMESTAMP、DBTIMEZONE、
LOCALTIMESTAMP、SESSIONTIMEZONE、
SYSDATE、SYSTIMESTAMP、UID、USER 和 USERENV）- 调用用户定义的函数
- 取消引用 REF 列（例如，使用 DEREF 函数）
- 嵌套表列或属性
- 伪列 CURRVAL、NEXTVAL、LEVEL 或 ROWNUM
- 未完全指定的日期常量
For 10g Release 2 (10.2), see constraint, and for 11g Release 2 (11.2)see constraint.
对于10g 第 2 版 (10.2)，请参阅约束，对于11g 第 2 版 (11.2)，请参阅约束。
Remember that an integrity constraint is a statement about table data that is alwaystrue.
请记住，完整性约束是关于始终为真的表数据的声明。
Anyway: I don't know exactly what you are trying to achieve but I think you can use triggersfor this purpose.
无论如何：我不确切知道您要实现的目标，但我认为您可以为此目的使用触发器。
回答by Jeff
Each and every time the record is updated SYSDATE will have a different value. Therefore the constraint will validate differently each time. Oracle does not allow sysdate in a constraint for that reason.
每次更新记录时，SYSDATE 都会有不同的值。因此，约束每次都会以不同的方式验证。由于这个原因，Oracle 不允许在约束中使用 sysdate。
You may be able to solve your problem with a trigger that checks if CloseDate has actually changed and raise an exception when the new value is not within range.
您可以使用触发器来解决您的问题，该触发器检查 CloseDate 是否实际更改并在新值不在范围内时引发异常。
And: What is (StartDate > (CloseDate + (SYSDATE + 730))))? You cannot add dates.
并且：是什么(StartDate > (CloseDate + (SYSDATE + 730))))？您不能添加日期。
And: StartDateneeds to be afterCloseDate? Is that not weird?
并且：StartDate需要在CloseDate? 这不奇怪吗？
回答by SriniV
Write sysdate into a column and use it for validation. This column might be your audit column (For eg: creation date)
将 sysdate 写入一列并将其用于验证。此列可能是您的审核列（例如：创建日期）
CREATE TABLE "AB_EMPLOYEE22"
(
"NAME" VARCHAR2 ( 20 BYTE ),
"AGE" NUMBER,
"SALARY" NUMBER,
"DOB" DATE,
"DOJ" DATE DEFAULT SYSDATE
);
Table Created
ALTER TABLE "AB_EMPLOYEE22" ADD CONSTRAINT
AGE_CHECK CHECK((ROUND((DOJ-DOB)/365)) = AGE) ENABLE;
Table Altered
回答by Wernfried Domscheit
You can achieve this when you do a little cheat like this:
当你做这样的小作弊时，你可以做到这一点：
CREATE OR REPLACE FUNCTION SYSDATE_DETERMINISTIC RETURN DATE DETERMINISTIC IS
BEGIN
RETURN SYSDATE;
END SYSDATE_DETERMINISTIC;
/
CREATE TABLE Table1 (
s_date DATE,
C_DATE DATE GENERATED ALWAYS AS ( SYSDATE_DETERMINISTIC() )
);
ALTER TABLE Table1 ADD CONSTRAINT s_check CHECK ( s_date < C_DATE );
Of course, function SYSDATE_DETERMINISTICis notdeterministic but Oracle allows to declare this anyway.
当然，功能SYSDATE_DETERMINISTIC是不确定性的，但Oracle允许申报这个反正。
Perhaps in future releases Oracle becomes more intelligent and will not allow such tricks anymore.
也许在未来的版本中，Oracle 会变得更加智能，不再允许使用此类技巧。
回答by hmmftg
I don`t recommend sing triggers as constraint and to raise exceptions, Instead you can use a column to store SYSDATEas register date(if you already have it then you can use it) and then your constraint compares this column instead of SYSDATE
我不建议将触发器作为约束并引发异常，相反，您可以使用一列将SYSDATE存储为注册日期（如果您已经拥有它，那么您可以使用它），然后您的约束比较此列而不是SYSDATE
ALTER TABLE Table1
ADD (REGISTER_DATE DATE);
CREATE OR REPLACE TRIGGER trg_check_dates
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
BEGIN
:new.REGISTER_DATE := SYSDATE;
END;
ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > REGISTER_DATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= REGISTER_DATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (REGISTER_DATE + 730))));
