Check Constraint on Date Of birth Column in oracle

Question

0.00/5 (No votes)

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 22:45pm

Member 10192073

Updated 9-Jan-16 22:51pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2016-01-09T22:51:00

Solution 1

On simple way is to create a trigger to do the check. Consider the following

SQL

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[^]

Posted 9-Jan-16 22:51pm

Wendelius

Updated 9-Jan-16 22:52pm

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

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.