Question about numeric keys...

[prognoob]

how do they work?
so i saw someone posting like...

Create Table login
 (Id int(4) NOT NULL,
  UserName varchar(20) NOT NULL,
  Password varchar(6) NOT NULL,
  securityQuestion varchar(50) NOT NULL,
  SecurityAnswer varchar(50) NOT NULL,
     Primary key (Id));

This person is clearly using numeric key...
but how do they work?
If I need information regarding a certain person's username and password... how is numeric key going to help the query?
any help would be greatly appreciated

[Atli]

Are you talking about using a numeric field as a primary key?

Primary keys are meant to be a unique value for each row; a way to identify a single row out of the rest, even if all other fields in the table contain identical data.
Integer values are perfect for this, as they can be automatically incremented for each row and they take a relatively small amount of disk space, compared to strings and such.

You could of course use a string value as a primary key, for example: a username, but usernames usually take up a lot more space than integers, which in large relational databases will take up a lot of extra disk space, as all your foreign key's will be strings rather than integers.

There are probably many more reasons that I am to tired to remember, but just the foreign key thing should be reason enough to use integers as primary keys.

[pbmods]

Heya, prognoob.

The main benefit to using a numeric primary key is that it is unchanging, and it never needs to be changed.

One problem with using, for example, the Username as the primary key, in addition to the warnings Atli posted, is that you will run into all kinds of headaches if your User wants to change his Username! Using a non-data primary key means that you can edit any of the data fields in your table, and you don't have to worry about your foreign keys going out of sync.

Once caveat about the table that you're looking at is that the `id` only has 4 digits, and it doesn't auto_increment. The problems with this setup are that you can only have 9999 (well, technically 10,000) Users. Also, you would have to manually increment the counter each time.

A better way to create this table would be:

CREATE TABLE
        `Users`
        (
            `Id`
                BIGINT(20)
                UNSIGNED
                NOT NULL
                AUTO_INCREMENT,
            `UserName`
                VARCHAR(60)
                NOT NULL,
            `Password`
                CHAR(40)
                NOT NULL,
            `SecurityQuestion`
                VARCHAR(50)
                NOT NULL,
            `SecurityAnswer`
                VARCHAR(50)
                NOT NULL,
            PRIMARY KEY
                (`Id`)
        )
        ENGINE=MyISAM
        DEFAULT CHARSET=utf8;
 

Note the following changes:

• The table has been renamed to `Users`, because `login` implies that you are using this table to track login sessions, which is not the case.
• `SecurityQuestion` is capitalized (probably a typo).
• `Password` is now a CHAR(40) instead of a VARCHAR(6). Why? Because when you save a password, you want to encrypt it, and as it happens, you can use SHA1(), which is very secure and always results in a 40-character string.