David:
Is there possibly a normalization issue here? To wit: Each VEHICLE may have only one DRIVER at any one point in time. Each DRIVER may have only one VEHICLE at any one point in time. But: Each VEHICLE may have many DRIVERs over time Each DRIVER may have many VEHICLEs over time. So maybe a DRIVER2VEHICLE intermediate table would solve the problem, and provide a record of who drove what when? An "flag-active" toggle would indicate the assignment and prevent duplicate assignment D2V PK: d2vID FK: drvID FK: vID d2vDateTimeLock d2vDateTimeRelease xFlagActive Or am I missing the point? I'm just sayin' Bruce Chitiea SafeSectors, Inc. eCondoMetrics From: [email protected] [mailto:[email protected]] On Behalf Of David Blocker Sent: Tuesday, July 30, 2013 2:02 PM To: RBASE-L Mailing List Subject: [RBASE-L] - An unresolved RULES issue from earlier email Hi friends I just realized as I temporarily moved forward on my project that an issue I wrote to the list about several weeks ago never was resolved. I?m trying again to see if anyone has an idea for a solution. Here?s the Structure: VEH (vehicles) table has primary key column VID (vehicle ID) Table DRIVERS (people who drive the vehicles) has Foreign key column VID. When a driver is assigned a vehicle, the VID column for that driver?s row is set to the VID value. However, there are two wrinkles here: 1) A Driver may NOT currently be assigned a vehicle, so VID could be NULL 2) If Driver A has a certain vehicle, say VID = 1, Driver B should NOT be allowed to get that vehicle. In other words, NO TWO RECORDS in Drivers should have the same value for VID So the rule I want would: 1) Permit Nulls 2) Forbid two rows in Drivers from having the same value. A rule is needed, because a Unique Key for VID in Drivers cannot be set up unless the column has a NOT NULL constraint. Here?s the rule that I tried based on some emails back and forth with Bill Downall: RULES 'Vehicle ID must be either blank or unique: two drivers cannot + be assigned to the same vehicle' + FOR Drivers FAILS + WHERE VID IS NOT NULL AND VID IN + (SELECT VID FROM DRIVERS d2 WHERE d2.VID = Drivers.VID AND + d2.DCode <> Drivers.DCode) This does NOT work. R:Base DOES allow null values, but it also allows me to set multiple rows to the same VID value. Any ideas? David Blocker David Blocker Email: [email protected] <mailto:[email protected]> Phone: 781-344-1920 Cell: 339-206-0261
