Indeed - is good form to assign the locid
field as a foreign key in the houses
table, provided that locid
is the primary key (PK) of the location
table. The locid
serves as a unique identifier for each location, and referencing it from other tables helps maintain referential integrity.
Regarding your concern about identity reset, since locid
is defined as smallint
and not as an identity column, there should be no automatic resetting of values unless explicitly done through some process (like reseeding). If locid
were an identity column, resetting the identity would only affect newly inserted records, not existing ones. However, if you're worried about potential accidental reseeding or manipulation, you could:
- Avoid using identity columns for
locid
if your current setup does not rely on automatically generated values. - Use location codes: If
location_code
is a stable and more meaningful identifier, you could reference it in thehouses
table instead oflocid
.
If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.
hth
Marcin