I assume your DRIVERS table has an ID column (I'll call it DID) Update all NULL DRIVERS.VID entries to (0-DID)
Create a UNIQUE Constraint on DRIVERS.VID So, a negative VID means no assignment and cannot reference any vehicle. Rules are slow, avoid them. Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of David Blocker Sent: Tuesday, July 30, 2013 4: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
