Not entirely sure of the context of your question, but let me give an example of where I have used this in the past. Assume a Salary history table that has 4 columns:
EmployeeID int not null -- FK to Employee table
Salary money not null
DateFrom datetime not null -- this is the start date/time of the salary
DateTo datetime null -- this is the end date/time of the salary
You could use a value of
null in the
DateTo to signify that this salary has not ended yet, i.e. is the current salary. By enforcing a unique constraint on that field, only one salary history could be deemed as 'current'. Together with other constraints to disallow overlapping date ranges, this would ensure that DateTo be either unique, or null.
This is an over-simplified and trivial example, but it shows how it can be used.
Jan 01 '12 at 02:39 PM
Kev Riley ♦♦