Deleting relationship also deletes primary key

I have the following tables defined as follows:

  • Lookup_ColorType (ColorType, Description)
  • Lookup_ObjectType (ObjectType, Description)
  • Object_Color (ObjectType, ColorType)

For the lookup tables, the first field is the primary key while in the third, both fields represent the primary key. What happened was when creating an FK relationship between Lookup_ObjectType and Object_Color, I got the parent and child reversed causing the following change in Lookup_ObjectType: (ObjectType, Description, ColorType). Obviously, had I discovered it immediately I would have simply clicked Undo. However, now as I attempt to remove the relationship, not only does it remove ColorType from Lookup_ObjectType, but it also removes ObjectType (the PK) from it as well! This has a trickle-down affect on all tables that reference Lookup_ObjectType.

Is there a way that I can remove a relationship without also removing the columns? Why would it remove a column that's the table's PK?

  • When you delete the relationship, in the dialog box there is a checkbox to 'Make Foreign Keys Native'.  If you check that box, any foreign keys propagated to the child table will be retained.  If the box is unchecked, the columns are removed.

Reply Children
No Data