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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.