Sql-server – Why use an int as a lookup table’s primary key

primary-keysql server

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).

I understand that using a nvarchar(50) rather than an int would use way more space if it is linked to a table with many records.

On the other hand, using the lookup value directly would basically save us doing a join. I can imagine this would be a big saving if the join is always required (We're working on a web app so this counts quite a bit).

What are the advantages of using a int primary key (specifically for a lookup table) other than it being "the standard thing to do"?

Best Answer

The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes your code simpler. If everything is integer → integer relations internally, you're covered.

Just read your comment to Sandy - perhaps in this case what you really want is a Check Constraint, not a foreign key/lookup table, e.g.:

create table icecream (flavour varchar(10))
go
alter table icecream add constraint ck_flavour check (flavour in ('Orange', 'Pista', 'Mango'))
go
insert into icecream (flavour) values ('Orange')
go
insert into icecream (flavour) values ('Vanilla')
go

Run this and you get:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_flavour". The conflict occurred in database "GAIUSDB", table "dbo.icecream", column 'flavour'.
The statement has been terminated.

This is an efficient, high-performance method, but the disadvantage of course is that adding a new flavour means a code change. I would advise against doing it in the application - because then you need to do it in every app that connects to this DB, this is the cleanest possible design because there is only a single code path for doing validation.

Related Question