Hi all, I've had a weird problem with constraints on a MS SQL 7 box. We have a constraint on a table called subscription which goes as follows:
([start_date] <= [end_date] or [end_date] is null and [subscription_level_id] = 1) which basically enforces that the end date of a subscription is after the start date except for one condition where the end date can be null. However, we have rows which have subscription_level_id=2 and end_date=null. We can confirm it by running the following query: select * from subscription where subscription_level_id<>1 and end_date is null But if you tack the constraint condition on the end you get no rows back, which means those rows must be breaking the constraint. We have removed and reapplied the constraint, with "check existing data" switched on, and it happily does so. What are we doing wrong? There are other constraints on the table, but it should AND them, right? And I am editing the constraints through Enterprise Manager - bad idea? David -- David Cummins Systems Developer Ubiquity Software Ltd. Ph: (09) 309-1921 ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
