How about skipping the check constraint and using a Unique, Filtered index?
CREATE UNIQUE INDEX UFI_NPIOrgAddressAvailability_NPIID_Default4Scheduling ON dbo.NPIOrgAddressAvailability (NPIID)
WHERE Default4Scheduling = 1;
Name the index per your local policy, of course. I just gave it a sort of default name.
Assuming you're using SQL 2008 (as per the forum you posted in), that should be an option. Try it and see if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon