sql primary key auto increment

Question

Is having a primary key that auto increments on each new row necessary? for me this number is getting quite long and I'm not even using it for anything.
I can imagine that with gradual user activity on my site new rows will be added (I am only testing atm with just 2 alfa test users and already the number has auto incremented to over 100), eventually this number could reach silly proportions (example: 10029379000577352881086) and not only slow the site down (effecting user experience) but also could inevitably push my site over its quota (exceeding its allowed size (laymen's))

really is this needed?

If a 4 byte int is insufficient to act as a primary key on your table, you have bigger problems than the primary key. You're now dealing with a database table that has 4 trillion rows. Assuming that each row has another 50 bytes of data, you're looking at a single database table that is 200 GB. Your test users have used 100 rows of the table. This means that if every single person on the planet used your application in the same way that your users had, you would only be 15% through the potential values of your primary key. — Marc Talbot, Mar 15 '12 at 04:21

score 2 · Accepted Answer · edited May 23 '17 at 12:07

If you have some field/column (or combination of columns) which can be a primary key, use that, why use Auto increment. There are school of thoughts which believe using a mix of both. You could search for surrogate keys and you may find this answer interesting Surrogate vs. natural/business keys

For size quota problem, practically I don't think the maximum auto increment value would cause your site to go over data limit. If it is of int type it will take 4 bytes, regardless of the value inside. For SQL server int type could contain values ranging from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). Here is the link for that

Note that you can also use identity wit BIGINT (64 bit). – StuartLC Mar 15 '12 at 04:23 — StuartLC, Mar 15 '12 at 04:23

score 1 · Answer 2 · answered Mar 15 '12 at 04:10

You need a way to uniquely identify each record in your table.

If you have that already -- say a user-ID or email-address -- then you don't necessarily need that auto-incrementing field.

Note: If you don't already have a unique constraint on that field, you should add one so that duplicate data cannot be entered into the table.

Warning: If you decide to get rid of it, be sure that no other tables are using it.

score 0 · Answer 3 · answered Mar 15 '12 at 04:14

0

can't you user multiple columns to get a composite key instead of that? just a hint.

answered Mar 15 '12 at 04:14

Darshana

2,462
6
28
54

score 0 · Answer 4 · answered Mar 15 '12 at 05:14

You do need a key that identifies every row. But a key doesn't have to be a number that "auto-increments" for every row. The fact that a few people seem to think incrementing numbers are always a good idea for keys is probably a consequence either of carelessness or a lack of appreciation of database fundamentals, sound design and data integrity.

score -2 · Answer 5 · edited May 23 '17 at 11:51

-2

primary key is not always necessary to have for a table . for your question check my answer:

when and when not primary key should use

edited May 23 '17 at 11:51

Community

1
1

answered Mar 15 '12 at 04:15

Pranav

8,563
4
26
42

sql primary key auto increment

5 Answers5