oracle 检查约束以确保日期不在未来？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4739298/
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
Check constraint for making sure a date is not in the future?
提问by solent_matt
I'm trying to create a table that records changes made to the estimated hours of another table. The database as a whole is a project management system for a company to assign work to its employees and create invoices for the customer.
我正在尝试创建一个表来记录对另一个表的估计小时数所做的更改。数据库作为一个整体是一个项目管理系统，供公司为其员工分配工作并为客户创建发票。
Currently I have:
目前我有：
CREATE TABLE task_history
(
task_history_id NUMBER(5),
previous_est_hours NUMBER(3,1),
change_date DATE,
reason_for_change VARCHAR2(50),
task_id NUMBER(5),
CONSTRAINT TASKHIST_TASKHISTID_PK PRIMARY KEY (task_history_id),
CONSTRAINT TASKHIST_TASKID_FK FOREIGN KEY (task_id) REFERENCES task(task_id),
CONSTRAINT TASKHIST_TASKID_NN CHECK (task_id IS NOT NULL),
CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate)
);
change_date must not be a future date, it must be either today or in the past. The last check constraint is the problem. As I understand it you cannot use the sysdate because of a reason I've forgotten, but you can't. I've also tried GETDATE() and every other variant I've found online. How can I do this presumably simple task?
change_date 不能是将来的日期，它必须是今天或过去。最后一个检查约束是问题。据我了解，由于我忘记的原因，您不能使用 sysdate，但您不能。我还尝试了 GETDATE() 和我在网上找到的所有其他变体。我怎样才能完成这个可能很简单的任务？
回答by Justin Cave
You can't call a function from a check constraint so the most natural approach would be to define a trigger on the task_history table, i.e.
您不能从检查约束调用函数，因此最自然的方法是在 task_history 表上定义触发器，即
CREATE OR REPLACE TRIGGER task_change_date_in_past
BEFORE INSERT OR UPDATE ON task_history
FOR EACH ROW
BEGIN
IF( :new.change_date > sysdate )
THEN
RAISE_APPLICATION_ERROR( -20001, 'Change date must be in the past' );
END IF;
END;
回答by Rob van Wijk
In 11g, it is possible to use check constraints with sysdate: http://rwijk.blogspot.com/2007/12/check-constraints-with-sysdate.html
在 11g 中，可以对 sysdate 使用检查约束：http: //rwijk.blogspot.com/2007/12/check-constraints-with-sysdate.html
Prior to 11g you should use Justin's approach.
在 11g 之前，您应该使用 Justin 的方法。
Regards,
Rob.
问候，
罗伯。
回答by RichardTheKiwi
CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate)
This would be really problematic as a Constraint.
Consider what would happen if one were to update a row (some other column) or deactivate/reactive the constraint. It won't check against the original date, but against the currentSYSDATE!
作为约束，这将是非常有问题的。考虑如果要更新一行（其他一些列）或停用/激活约束会发生什么。它不会检查原始日期，而是检查currentSYSDATE！
I would advocate adding another column to the table, defaulted to SYSDATE, and building a constraint or TRIGGER that will compare the new column (stored SYSDATE) against change_date.
我主张向表中添加另一列，默认为 SYSDATE，并构建一个约束或 TRIGGER，将新列（存储的 SYSDATE）与 change_date 进行比较。
回答by Antoine Aubry
I think that you can define a user-defined function that performs that check and use it in the check constraint.
我认为您可以定义一个用户定义的函数来执行该检查并在检查约束中使用它。
