Check Constraint on Date of Birth

CHECK constraint on date of birth?

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 SYSDATE is inherently non-deterministic since the returned 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'll get an error

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 CHECK constraint 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.

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;

Make a constraint for SYSDATE minus a date of birth

Starting in Oracle 11g, while you can't directly use sysdate in a constraint, you could get the same effect with a virtual column:

create function over_18 ( 
    p_birth_date in User.dateOfBirth%type
) return varchar2 deterministic is

begin
    return case when months_between(sysdate, p_birth_date) /12 > 18 then 'Y' else 'N' end;
end over_18;
/

alter table User add (over_18_ind as (cast(over_18(dateOfBirth) as varchar2(1))));
/

alter table User add constraint check_age check (over_18_ind = 'Y');
/

Based on an article here.
Even if Oracle allowed you to use sysdate in a constraint, your constraint wouldn't work anyway, since the formats you converted your dates to can't be implicitly cast back to numbers to subtract (I supplied an alternate age check).

I am trying To add Check constraint for checking age and date of birth

You cannot use SYSDATE in check constraint. According to Oracle Documentation - Check Constraint

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

So, you can use Trigger to get your desired output in this case. Here, is the trigger which will work fine as per your requirement:

CREATE OR REPLACE TRIGGER trg_check_date
BEFORE INSERT OR UPDATE ON AB_EMPLOYEE22
FOR EACH ROW
BEGIN

  IF(ROUND((sysdate-nvl(:NEW.DOB,:OLD.DOB))/365) <> nvl(:NEW.AGE,:OLD.AGE))
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Your Date of Birth and Age do not match');
  END IF;

END;

If you find any difficulty in this trigger, please feel free to write in comments.

how to add Check constraints on mysql that age calculate from Date of birth field and validate that age is greater than 18

MySQL does not support check constraints. So, the only way to do this in MySQL is to create a trigger or wrap the insert in a stored procedure.

The specific logic that you would use is:

dob <= curdate() - interval 18 year

So, in almost any other database, this would look something like:

alter table t add constraint chk_t_dob check (dob <= curdate() - interval '18' year);

(Note: date operations vary among databases.)

Using date in a check constraint, Oracle

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.

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;