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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close