SQL 检查出生日期的限制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8424900/
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 on date of birth?
提问by Jatinder Singh
I am creating my employee table in Oracle and for emp_date I would like to make it so the date of birth is not way back in the past and can not be set in the future?
我正在 Oracle 中创建我的员工表，对于 emp_date 我想让它这样出生日期不会回到过去并且将来不能设置？
Is this too complex to implement in the CREATE TABLE statement? if so then I guess I have to skip it because that is the part where I have to create the constraints.
这是否太复杂而无法在 CREATE TABLE 语句中实现？如果是这样，那么我想我必须跳过它，因为这是我必须创建约束的部分。
, emp_dob DATE NOT NULL
CREATE TABLE employee(emp_id NUMBER(4) PRIMARY KEY
, emp_name VARCHAR2(40) NOT NULL
, emp_address VARCHAR2(60) NOT NULL
, emp_tel NUMBER(11) NOT NULL
, CONSTRAINT emp_tel_length CHECK (LENGTH(emp_tel) BETWEEN 9 and 11)
, emp_dob DATE NOT NULL
, CONSTRAINT check_date_of_birth CHECK (emp_dob BETWEEN DATE '1950-01-01' AND sysdate))
回答by Justin Cave
Check constraints must be deterministic. That is, a particular row must always satisfy the constraint or it must always fail to satisfy the constraint. But SYSDATEis inherently non-deterministic since the returned value is constantly changing. Thus, you cannot define a CHECKconstraint that calls SYSDATEor any other user-defined function.
检查约束必须是确定性的。也就是说，特定行必须始终满足约束，否则必须始终无法满足约束。但SYSDATE本质上是不确定的，因为返回的值在不断变化。因此，您不能定义CHECK调用SYSDATE或任何其他用户定义函数的约束。
If you try to reference SYSDATEin the constraint definition, you'll get an error
如果你尝试SYSDATE在约束定义中引用，你会得到一个错误
SQL> ed
Wrote file afiedt.buf
1 create table t(
2 birth_date date check( birth_date between date '1900-01-01' and
3 sysdate )
4* )
SQL> /
sysdate )
*
ERROR at line 3:
ORA-02436: date or system variable wrongly specified in CHECK constraint
You could create a CHECKconstraint where both the minimum and maximum date were hard-coded but that would not be particularly practical since you'd have to constantly drop and recreate the constraint.
您可以创建一个CHECK约束，其中最小和最大日期都是硬编码的，但这不是特别实用，因为您必须不断删除并重新创建约束。
SQL> ed
Wrote file afiedt.buf
1 create table t(
2 birth_date date check( birth_date between date '1900-01-01' and
3 date '2011-12-08' )
4* )
SQL> /
Table created.
The practical way to enforce this sort of requirement would be to create a trigger on the table
强制执行此类要求的实用方法是在表上创建触发器
CREATE OR REPLACE TRIGGER check_birth_date
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN
IF( :new.emp_dob < date '1900-01-01' or
:new.emp_dob > sysdate )
THEN
RAISE_APPLICATION_ERROR(
-20001,
'EMployee date of birth must be later than Jan 1, 1900 and earlier than today' );
END IF;
END;
回答by wildplasser
Another way would be to create a domain birthdate, with the constraint built into the domain. This will allow you to reuse the same type in other table definitions.
另一种方法是创建域的生日，并将约束内置到域中。这将允许您在其他表定义中重用相同的类型。
CREATE DOMAIN birthdate AS date DEFAULT NULL
CHECK (value >= '1900-01-01' AND value <= now())
;
CREATE TABLE employee
( empno INTEGER NOT NULL PRIMARY KEY
, dob birthdate NOT NULL
...
);
回答by SriniV
What about another workaround
另一个解决方法呢
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 A.K Nayak
You need to create a table with DATE_OF_BIRTH and the PRESENT_DATE i.e I am used DATE_JOINING and then use an AGE default value by subtracting the DATE_JOINING minus DATE_OF_BIRTH.
您需要使用 DATE_OF_BIRTH 和 PRESENT_DATE 创建一个表，即我使用的是 DATE_JOINING，然后通过减去 DATE_JOINING 减去 DATE_OF_BIRTH 来使用 AGE 默认值。
Finally set CHECK constraint to AGE is it greater than your requirement i.e I am set 20.
最后将 CHECK 约束设置为 AGE 是否大于您的要求，即我设置为 20。
See below
见下文
Createing Table Employee
创建表员工
CREATE TABLE EMPLOYEE(
NAME VARCHAR(20) NOT NULL,
DOB DATE NOT NULL,
DATE_JOINING DATE DEFAULT NOW(),
AGE INTEGER DEFAULT TIMESTAMPDIFF(YEAR,DOB,DATE_JOINING) CHECK(AGE>20)
);
Inserting Data to the Table
向表中插入数据
INSERT INTO EMPLOYEE(NAME,DOB) VALUES('NAYAK','1998-2-3');
Query OK, 1 row affected, 1 warning (0.128 sec)
Age is greater than 20 so it is inserted.
年龄大于 20，因此插入。
SELECT * FROM EMPLOYEE;
+-------+------------+--------------+------+
| NAME | DOB | DATE_JOINING | AGE |
+-------+------------+--------------+------+
| NAYAK | 1998-02-03 | 2019-10-15 | 21 |
+-------+------------+--------------+------+
1 row in set (0.001 sec)
Now Insert a DOB less than 20 years
现在插入不到 20 年的 DOB
INSERT INTO EMPLOYEE(NAME,DOB) VALUES('NIRAJAN','2005-2-3');
ERROR 4025 (23000): CONSTRAINT `EMPLOYEE.AGE` failed for `NAYAK`.`EMPLOYEE`
Not Inserted because of check.
由于检查未插入。
回答by Pritha Upadhyay
You can use interval directly:
您可以直接使用间隔：
ALTER TABLE "students"
ADD CONSTRAINT "students_must_be_over_18" CHECK (
CURRENT_TIMESTAMP - "date_of_birth" > INTERVAL '18 years'
);
