what is the advantage of giving null value to unique constrain

what is the advantage of giving null value to unique constraint ?
more ▼

asked Jan 01, 2012 at 02:56 AM in Default

Farhan Ali gravatar image

Farhan Ali
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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.
more ▼

answered Jan 01, 2012 at 02:39 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

+1 for a nice one :)
Jan 01, 2012 at 08:41 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 01, 2012 at 02:56 AM

Seen: 923 times

Last Updated: Jan 01, 2012 at 09:47 PM