SubTable Deletions/Relationships

[isorders]

Sorry this is so long... but the question is can I only delete the ColorID from a Car Record if I delete a color in the Color Table. The Car table and color tables are joined on ColorID. Every time I try it, it deletes the whole car record, not just the car's ColorID.

I have what I call sub tables setup with repetitive type info them. They are joined to the main tables so user input is consistent.
Ex.
stblColor
ColorID(autonumber)-1
Color-Red
Joined by ColorID with Ref Integ and Cascade Update/Delete checked to...
tblCar
CarID(autonumber)-5
ColorID-1

So Car 5 is Red.

The user selects the color red from a combo box. But if they type in something not in the list (purple) it prompts them "Do you want to enter Purple in the list." If they say yes it puts Purple into stblColor and the combo box then displays Purple as an option.
stblColor
ColorID-2
Color-Purple

So now
CarID-5
ColorID-2
...is now Purple.
If the user misspells a color or does not want the option available anymore and wants to delete it from stblColor....
is there a way to delete the color purple from stblColor without deleting the whole car record, I just want the color field in tblCar to be cleared of any color ID.

 

[cmmrfrds]

One way to maintain your referential integrity is to put a colorid in the color table that means no color.

For instance instead of red or purple it would say no color. So, the user would change from purple to no color.

 

[isorders]

The issue is not putting in no color, but the wrong color.
Maybe this is a better Example.
Someone types in Azure Blue for the car color, but the database admin says I don't want Azure Blue to be an option, I only wany Sky Blue, so they say remove the Azure Blue option from the drop down list(stblcolor), but since Azure Blue is already recorded in the car record, if its deleted it deletes the whole car record with it.
I anticipate people typing in similar info like SkyBlue instead of selecting Sky Blue and I wanted a way keep the drop down lists clean. I guess I could query all records on the offending option and change them in tblCar, then delete it from the stblColor. Since color was not a required field it would be nice to just clear the field of Azure Blue, or SkyBlue info.

 

[cmmrfrds]

If the database administrator is dictating the valid colors then that person should be adding the colors not the end user. The end user should be picking the color from a list and that is the only way they can enter a color. DON'T ALLOW the user to select a wrong color. If they need to enter something and don't know the color then have an unknown option.

Get rid of cascading delete in the color relationship since it is not valid for this type of relationship. If a color goes out of style then disable it in the color table and individually change the detail records if/when it makes sense. Normally you would maintain some kind of historical references on the color.