CHECK date limit on birth?

Question

I create my employee table in Oracle and for emp_date, I would like to make sure that the date of birth does not return to the past and cannot be set in the future?

Is it too hard to implement in a CREATE TABLE statement? if so, I think I need to skip it because this is the part where I need to create constraints.

, 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)) 

Answer 1

Check constraints must be deterministic. That is, a particular line must always satisfy the constraint, or it must always not satisfy the constraint. But SYSDATE is inherently non-deterministic since the return value is constantly changing. Thus, you cannot define a CHECK constraint that calls SYSDATE or any other user-defined function.

If you try to reference SYSDATE in the constraint definition, you will receive an error message

 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 can create a CHECK constraint where the minimum and maximum dates were hardcoded, but that would not be particularly practical, since you had to constantly reduce and recreate the constraint.

 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. 

A practical way to enforce this requirement is to create a trigger in 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; 

Answer 2

Another way is to create a domain’s birth date with a restriction 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 ... ); 

Answer 3

What about another workaround

Write sysdate to the column and use it for validation. This column may be your audit column (e.g. creation date)

 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 

Answer 4

You need to create a table with DATE_OF_BIRTH and PRESENT_DATE, i.e. I use DATE_JOINING and then use the default AGE value by subtracting DATE_JOINING minus DATE_OF_BIRTH.

Finally, set the CHECK limit to AGE if it is more than your requirement, i.e. I have been set to 20.

See below

Create Employee Table

 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) ); 

Insert data into a table

 INSERT INTO EMPLOYEE(NAME,DOB) VALUES('NAYAK','1998-2-3'); Query OK, 1 row affected, 1 warning (0.128 sec) 

Age over 20, so it is inserted.

 SELECT * FROM EMPLOYEE; +-------+------------+--------------+------+ | NAME | DOB | DATE_JOINING | AGE | +-------+------------+--------------+------+ | NAYAK | 1998-02-03 | 2019-10-15 | 21 | +-------+------------+--------------+------+ 1 row in set (0.001 sec) 

Now enter DOB in less than 20 years

 INSERT INTO EMPLOYEE(NAME,DOB) VALUES('NIRAJAN','2005-2-3'); ERROR 4025 (23000): CONSTRAINT 'EMPLOYEE.AGE' failed for 'NAYAK'.'EMPLOYEE' 

Not inserted due to validation.