good database design - is it safe to use identity column in table a in table b

Darryl Hoar 181 Reputation points
Oct 10, 2024, 9:05 PM

Greetings,

I am trying to make my database better.

I have a table called location. It has the following fields defined:

locid (PK, smallint, not null)

location_code (smallint, not null)

description (nvarchar(50), null)

Several other tables in my database need to reference records in the location table.

Lets say one of those tables is named houses.

Is it good form to assign the location locid field value to a field in table houses ?

My concern is that the identity can be reset.

thanks for any guidance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,256 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Marcin Policht 31,145 Reputation points MVP
    Oct 10, 2024, 9:24 PM

    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:

    1. Avoid using identity columns for locid if your current setup does not rely on automatically generated values.
    2. Use location codes: If location_code is a stable and more meaningful identifier, you could reference it in the houses table instead of locid.

    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

    1 person found this answer helpful.
    0 comments No comments

  2. LiHongMSFT-4306 29,666 Reputation points
    Oct 11, 2024, 2:54 AM

    Hi @Darryl Hoar

    My concern is that the identity can be reset.

    Besides of the reset issue, identity columns can produce gaps when rows are deleted or when transactions are rolled back. And it is complex to manage identity values when you migrate data.

    Generally speaking, it is fine to use identity columns for primary and foreign keys, provided that you have measures in place to manage the concerns above.

    For alternative, you can consider using GUID or UUIDs. Also, you could use a combination of columns that naturally create uniqueness (e.g., LocationCode + Userid).

    Best regards,

    Cosmog


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.