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
