[SOLVED] CHECK constraint on date of birth?

CHECK constraint on date of birth?

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?

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.

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

Solution

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;