Most of the scenarios in Relational Database Management System Primary Key used will be of Integer Type why ?

Question

Its been habitual in most of the scenarios while developing a database design we set primary key as integer type for a unique identifier in the Table , Why not string ,float type for primary keys does this affect the accessibility of values or in plain words retrieval speed of values from the table will be slower ? Are there any specific reason ?

Answer 1

I suspect that it is because we can auto-increment integer values so it's easy to generate a new unique key for every insert.

Answer 2

An integer will use less disk space than a string, thus giving you a smaller index file to search through. This is important for large tables where you want to have as mucha s possible of the index cached in RAM.

Also, they can be autoincremented so you don't need to write your own routines to generate keys.

In database normalization you often want to have a technical key, a key that is only used to identify the row and not used for anything else. Then it's useful to have small key that is easily generated (through autoincrement).

Answer 3

Many common ORM (Object Relational Mapping) tools either force to use or at least recommend using integer as primary key.

Integer primary key also saves space compared to string and integer primary key is in some cases also faster. Sequences or auto increment fields make integer primary key generation easy at least if you do not work with distributed databases.

Answer 4

The primary key is supposed to be an index that can provide a unique way to access a specific row in a table. Primary keys can be most data types (in practical applications, float/double won't work too well), and primary keys can also be compound keys (comprised of several columns.)

If you carefully examine the data in the table, you might be able to find a data item that will be unique for every row in the table, thereby eliminating the requirement that you fabricate a key like the autoincrement integer that you find in some schemas.

If you're in a manufacturing environment it might be an alphanumeric field like part number or assembly identifier. Retail or warehousing applications might have a stock number or combination of stock number/shipment/manufacturer.

Generally, If some data in your table is supposed to be a unique identifier it probably will serve well as a primary key for your table.

Using data that exists in the table already completely eliminates the requirement to "make up" a value (such as the autoincrement column) and use it as the primary key. This saves space since it's one less column in the table and one less index on the table.

Yes, in my experience integer keys are almost always faster, since it's more efficient for the database engine to compare integers than comparing strings. Depending on the "uniqueness" of the data (technically called cardinality http://en.wikipedia.org/wiki/Cardinality%5F%28SQL%5Fstatements)), the effect of character vs. integer keys is nominal.

Character keys may degrade performance depending on the number of characters that the database needs to compare to determine whether keys are equal or not equal. In the pathological case, imagine a hundred-character field which differ only on the right hand side. One row has 100 A's. We need to compare this to a key with 99 A's and a B as the last character. Conceptually, databases compare character fields just like strcmp() (strncmp() if you prefer) from left to right.

good luck!

Answer 5

Hi,

These are some of the main reasons why i think we have integers/ numbers as primary keys.

1.Primary keys should be able to uniquely define your row and should be immutable. One of the problems with using real attributes (name etc..) is that they could change over time. To maintain relational integrity in such a case would be very difficult as this change needs to cascade to all the child records.

2.The size of the table and thereby the index would be smaller in case we use a number as a key for the tab.e

3.Since these are automatically generated using a sequence, we can be sure that the values would be unique under all circumstances.

Check this. http://forums.oracle.com/forums/thread.jspa?messageID=3916511&#3916511

Thanks, Rajesh

Answer 6

The only reason is for performance.

A logical database design should specify which "real" columns are unique, but when the logical design is transformed into a physical design, it is traditional to not use any of these "natural" keys as the primary key; instead, a meaningless integer column is added for this purpose - called a "surrogate key".

Normally the designer will add further unique constraints for the "real" uniqueness business rules as specified in the logical design.

This is because most DBMS's have trouble updating a primary key (e.g. due to performance issues when cascading the update to child tables). Some DBMS's might not be able to support non-integer primary keys at all.

Some side notes:

• There's no theoretical reason why primary keys should be immutable.

• This is nothing to do with normalization, which happens in the logical model (which should never have surrogate keys).

• Also, note that the idea of a "primary" key is not a relational concept - it is simply a way of denoting the "preferred" uniqueness constraint, perhaps for relational integrity - but there's nothing in the RM that says that you must use the same key for each child table.

• I've created natural keys as "Primary Keys" in Oracle databases before, albeit rarely. I've even had them used for foreign key constraints. Admittedly, they were either immutable, or I hand-wrote the update-cascade code; and I had trouble with one front-end application where the PK included a date column.

Bottom line: there is no theoretical requirement for surrogate keys, but they're much more practical than the alternative.