Click here to Skip to main content
13,289,626 members (58,078 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi i want to create a table that has the constraint on date Of Birth(DOB) Column.

means (today-Date of birth)>= 18 years

my table looks like

create table EmpRegular_dtls
(
Emp_Id NVARCHAR2(20) not null,
EmpName NVARCHAR2(20) not null,
DOB date not null,
CONSTRAINT chk_DOB check((round((sysdate-DOB)/365))>=18)
);


but i am getting a big error on this........

Quote:
SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 - "date or system variable wrongly specified in CHECK constraint"
*Cause: An attempt was made to use a date constant or system variable,
such as USER, in a check constraint that was not completely
specified in a CREATE TABLE or ALTER TABLE statement. For
example, a date was specified without the century.
*Action: Completely specify the date constant or system variable.
Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
which a bug permitted to be created before version 8.


so now please give me exact solution for this.
thanks
Posted 9-Jan-16 23:45pm
Updated 9-Jan-16 23:51pm
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

On simple way is to create a trigger to do the check. Consider the following
CREATE OR REPLACE TRIGGER trgEmpRegular_dtls
  BEFORE INSERT OR UPDATE ON EmpRegular_dtls
  FOR EACH ROW
BEGIN
  IF( ADD_MONTHS(:new.DOB, 18 * 12) < sysdate ) THEN
    RAISE_APPLICATION_ERROR( -20001, 'Person must be at least 18 years old.' );
  END IF;
END;

If the age is less than 18 years an exception is thrown and you can grab the message on the calling side and show it to the user.

More information about custom errors: Handling PL/SQL Errors[^]
  Permalink  
v2
Comments
Member 10192073 10-Jan-16 4:53am
   
its by trigger?we can not achieve this by check constraint?
please if you have exact solution via check constraint then please provide me.
i am learning constraint first then i will learn trigger.so that's why i need solution for check constraint
Mika Wendelius 10-Jan-16 6:27am
   
You cant use non-deterministic functions with constraints. This is why you can't use SYSDATE in a check constraint statement.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.171207.1 | Last Updated 10 Jan 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100