I did try that, but they must be logically the same, because EM actually removes those parentheses!
David -- David Cummins Systems Developer Ubiquity Software Ltd. Ph: (09) 309-1921 Robert Miller wrote: > > David, > > I think that you want to change your constraint to read as such: > > ([start_date] <= [end_date] or ([end_date] is null and > [subscription_level_id] = 1)) > > Notice the extra parentheses that are specifically grouping the end_date > and subscription_level_id together. > > Robert Miller > Promises Kept, LLC > www.promises-kept.com > email: [EMAIL PROTECTED] > direct: 480.998.2918 > fax: 413.845.7357 > > > -----Original Message----- > From: David Cummins [mailto:[EMAIL PROTECTED]] > Sent: Sunday, February 10, 2002 5:25 PM > To: SQL > Subject: Constraint weirdness > > 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 ______________________________________________________________________ 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
