Make a validate rule in a table

Hi

In access i can make a rule
like if i have a Coloumn to date
i can make a rule to say that this fields data
shall be > date

can i do this also in sql and how?

regards

alvin

You can create a Check Constraint on a column in a table. Check Constraints can compare a column's value to other values within the same row or to functions, but cannot compare to other rows.

In your example, are you asking how to compare a column named to_date to one called date? Or are you asking how to compare to_date to the value of the curent date returned from a function? There are some wrinkles with the latter, since a row that passes today, might be invalid tomorrow. The constraint will be re-evaluated if the row is modified, even if it is not the to_date field that is changed.

Constraints can be defined in the Create Table statement:

Create Table myTable(

from_date datetime Check( from_date < GetDate() ), -- Unnamed constraint using a function

to_date datetime

Constraint Range_Check Check( to_date > from_date ) -- Named table constraint comparing two columns

)

You can add Constraints with Alter Table as well. See the Books-online for more information.

Alter Table myTable Add Constraint Min_Range Check( DateDiff( dd, from_date, to_date ) > 10 )