Q
Problem solve Get help with specific problems with your technologies, process and projects.

Age constraint on a date column

I have created a table named Student, and under the field DateOfBirth, I want to use a table constraint so that the date of birth of the student is over 18. How do I do this?

Whether you can do this at all will depend on your database system. Some won't let you declare check constraints. Further, the syntax may involve a slightly different date functions.

create table Student
 ( StudentID integer
 , DateOfBirth date
 , constraint Over18
   check ( 
      year(current_date) - year(DateOfBirth) > 18
        or 
      year(current_date) - year(DateOfBirth) = 18
  and month(current_date) > month(DateOfBirth) 
        or 
      year(current_date) - year(DateOfBirth) = 18
  and month(current_date) = month(DateOfBirth) 
  and day(current_date) >= day(DateOfBirth)
         )
 )

Notice how the conditions are stated positively. The conditions in the constraint are tested when the row is first inserted, and also again if DateOfBirth is updated. The conditions must be true for the insert or update to proceed.

The last part of the constraint handles the student's birthday. What does "over 18" mean? Use "greater than" instead of "greater than or equal" to exclude the birthday.

For More Information


This was last published in November 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close