I thought you HAD figured this out. I copied your response to my notes document because I thought the rule syntax was clever:
RULES + 'Another driver is already assigned to this 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) Karen -----Original Message----- From: David Blocker <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Jul 30, 2013 4:02 pm Subject: [RBASE-L] - An unresolved RULES issue from earlier email Hi friends I just realized as I temporarily moved forward on my projectthat an issue I wrote to the list about several weeks ago never wasresolved. I?m trying again to see ifanyone 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 Foreignkey column VID. When a driver is assigned a vehicle, the VID column for thatdriver?s row is set to the VID value. However, there are two wrinkles here: 1) ADriver may NOT currently be assigned a vehicle, so VID could be NULL 2) IfDriver A has a certain vehicle, say VID = 1, Driver B should NOT be allowed toget that vehicle. In other words, NO TWORECORDS in Drivers should have the same value for VID So the rule I want would: 1) PermitNulls 2) Forbidtwo rows in Drivers from having the same value. A rule is needed, because a Unique Key for VID in Driverscannot be set up unless the column has a NOT NULL constraint. Here?s the rule that I tried based on some emails back andforth with Bill Downall: RULES 'Vehicle IDmust be either blank or unique: two drivers cannot + be assigned to the same vehicle' + FOR Drivers FAILS+ WHERE VID IS NOTNULL AND VID IN + (SELECT VID FROMDRIVERS 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 multiplerows to the same VID value. Any ideas? David Blocker David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
