>>Hi,
>>
>>I must be missing something simple. When I try to set the foreign key constraint, it fails. Here is the description:
>>1. I added new table PART_CLASS with primary column CLASS_PK (Int), Identity.
>>2. I added to PARTS table a column CLASS_PK (int), default 0
>>
>>I want to be sure that when/if the row/record in PART_CLASS is deleted, all referenced rows in PARTS set the value in columnd CLASS_PK to default (0).
>>
>>I think the constraint, when I set it up in SSMS, fails because table PARTS has many records and all of them have value 0 in the column CLASS_PK. But table PART_CLASS has no records. So SQL Server does not find a record in PART_CLASS of CLASS_PK value 0.
>>
>>How do you deal with this? TIA.
>
>I was wondering if I even need to set up a foreign key constraint on the field that user cannot and should not be able to change. That is, for the case described above, user will use the pull-down list of entries in the PART_CLASS table (combo control). So if user cannot "mistakenly" enter the "wrong" value into the column PARTS.CLASS_PK, why have constraint? What is a good database design practice?

Excellent question, Dimitry
I never let a user make unvalidated entries into a column that references a primary key in another column.
Pull downs can be a pain at times when there are a lot of entries in the pull down and the user knows the data by memory, so I tend to use a text box with a pull down button next to it. If the data entered is not valid, the pull down pops up.
However someone can come along and try to delete the row in the parent table and that's why people use foreign key restraints.
I personally prefer to do that programmatically so that I can control the user interface more so I don't use foreign key restraints.