We help IT Professionals succeed at work.

What does the "length" of a primary key refer to?

ariestav
ariestav asked
on
This is just a simple question for which I simply don't understand conceptually.  Let's say I have a user table in my db, and I want to make a Primary Key based off the UserID field.  I know how to make the index and how to ensure it is unique, but what I never understood is what "Key Length" referred to.  Shouldn't the length of the primary key match that of the field from which is was created?  

Any light that you can shed on the topic of key length (particularly for MySQL) is greatly appreciated.

Thanks!
Comment
Watch Question

Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
It means that your primary key will be long as you set. If you set int(2), this means thet your primary key will grown up to 99 because 100 has a length of 3.

Usually a length of 11 is used: 11 cipher give you a very large number of records, hm?

Author

Commented:
?  I don't understand what a cipher is.  What does "give me a very large number of records" mean in this context?  How does the key length impact how many records I can retrieve?  

At the moment the primary key in my users table is the userID field, and the key length is 0.  Not sure what that means. . .please help.

Thanks.
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Let say that you set max length of your primary key to 2: well, the max value your key will have will be 99

If you set it to max length = 6, the max value will be 999999

If you set it to 11 as susual, the max value will be 99999999999: this will be the max number of rfecord your table will can indicize
marqusG gives a very good explanation but in case there is still confusion here is another explanation.

First, I assume your UserID field is numeric.  In that case the definition of the field is an integer and a length.  int(11) means I can have any integer as long as there are 11 or less digits in the number.  Or, a number between and including 1 and 99,999,999,999.

With a primary key as described above, each row or record in your database will be numbered sequentially usually starting at 1 in the UserID field.

You could define your UserID field as int(5) and in that case your primary key values would run from 1 to 99,999.  If you tried to insert row number 100,000, in this example, you would get an error as there would not be any key values remaining.

UserID fields of an int() variety are usually set up to be unique and auto-increment so that you don't need to keep track of the next available number.

Now, you can also have  a primary key that is not numeric.  It could be a varchar(32), for example if you wanted to use a persons username as a primary key on the table since presumably the username would be unique.  In that case the 32 represents how many characters the field will hold.  You could have a virtually unlimited number of records because the number of unique combinations of 32 or fewer characters is a whole lot of possibilities.  Obviously, the auto-increment functions would not be applicable because the data is not numeric.


Hope that helps.
IT Supervisor
CERTIFIED EXPERT
Top Expert 2009
Commented:
Sorry, but you all are incorrect (at least when it comes to MySQL) and numeric values.

A signed INT field stores values from –2147483648 to 2147483647 regardless of the length argument.  INT(1) and INT(11) will hold the *same* range of values.  The length argument in this case is just there as a helper to GUI applications who may need to display the value in a certain sized element.  As a test, do this in MySQL:

CREATE TABLE test_int (id1 INT(1), id2 INT(11));
INSERT INTO test_int VALUES ('9999999999','9999999999');
SELECT * FROM test_int;

Open in new window


The output will be 2147483647 for both columns.  The field length argument here really doesn't mean anything.

The length argument *is* significant for VARCHAR() and CHAR() fields.

However, all of this is moot, since this isn't even what the original poster is asking for - they're asking about the length argument on a numeric PRIMARY KEY.  Again, this number is insignificant for numeric types, thus a zero is a perfectly fine value for a PRIMARY KEY (or INDEX or UNIQUE index) on a numeric field.

The key length, however, is significant for the various TEXT field types, as it will define how much of that field is to be considered for the KEY.  For example, you may have a VARACHAR(200) field for storing up to 200 bytes of data, but perhaps only the first 50 characters are needed to create a unique value.  In this case, you could define your KEY on that column to have a length of 50.

I know this is NOT the way Oracle and several other RDBMs work!  For those the other posters ARE 100% correct.  MySQL is just weird in its numeric fields. ;-)

Author

Commented:
Thank you for the clarification.  Yes, I am using NaviCat to design my tables, and noticed that the key length was set to zero, and yet, everything was being indexed appropriately.  Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.