Help choosing foreign key

Results 1 to 2 of 2

Thread: Help choosing foreign key

  1. #1
    Join Date
    Dec 1969

    Default Help choosing foreign key

    I asked this question in a very unclear way at the end of another thread, I&#039;ll try to re-phrase it. (sorry for the reposting)<BR><BR>TableA.Values (varchar) lists all the possible values for Column1 in TableB. I will use TableA to create a select box for inserting data into TableB, and also as a constraint in the database.<BR><BR>Which is better:<BR>1) Put an int identity primary key column in TableA and have TableB.Column1 reference that.<BR>2) Make TableA.Values be the primary key and have TableB.Column1 reference TableA.Values directly.<BR><BR>-joe<BR>PS: Using MS SQL Server, if that makes a difference.

  2. #2
    Join Date
    Dec 1969

    Default No, choosing a Primary Key

    The definition of a Primary Key is that it is (1) Indexed and (2) Unique (allows no duplicate values).<BR><BR>If your TableA.Value fulfills (or can fulfill) those requirements, then there is no reason to create a second, less meaningful, field to be used as the primary key.<BR><BR>Of course, if TableA.Value is a VARCHAR(2000) field, then you&#039;d likely be better off going ahead with the IDENTITY field, just because of the space you&#039;d save in TableB (each foreign key reference is now much smaller). Plus a search in an index based on VARCHAR(2000) would certainly be more memory intensive and slower than one based on an integer.<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts