Ref. Integrity and Indexes

[GT98SVO]

For a table with a unique set of character data(eg. varchar2). Cardinality could be high or low.

What is the difference between
1. PK constraint on a text field, then using this column as FK for other tables.

and

2. Creating an incrementing number field that is the PK and putting a UNIQUE constraint on the text field. Use the number field as the FK in other tables?

From what I can see, choice 1 is better because I wouldn't have to join the parent table to get the varchar2 field.

Is an index on a number field that much faster than an index on a varchar2 field.

 

[jee]

You have just entered the realm of religious wars in relational databases! There are all kinds of experts lined up on both sides of this issue!

Like just about every other database issue, there is no simple answer - as usual the answer begins "It depends...".

Solution 1 (PK on text column) seems simpler - does not add an additional column and has the added advantage of making the raw data in the "child" table more readable. ANd, as you said, you don't need to join to get the value of the text column when accessing the child tabe. However there can be problems... Will you ever need to change the value of the text column? It becomes more difficult if there are related rows in the child table. Also, are you sure that the text column values will always be unique?

Solution 2 (artificial numeric primary key) solves the problems of solution 1. With of course the disadvantage that an additional column is needed and when looking at the child table data, it is less obvious to which parent row a child row is related. If the text column is very long, the index will work better with solution 2.

I'll have to say that in most of my work I have tended towards Solution 2 in most cases. There are several instances that I started out with Solution 1 and changed to Solution 2 later.

 

[jimbopalmer]

numbers are almost always samller to save on disk than strings, so one SGA page of numbers will point to more rows of real data than one SGA page of varchar2 index will.

as a rule users will decide that "constants aren't, variables won't" so they come back to you and say we changed the equipment number of this change, it in all 78 tables we used it as a key

the inserts can be faster using a sequence than having to check for uniqueness, though you are rarely bound by the speed of initial inserts. I tried to remain child-like, all I acheived was childish.