Is it okay to have non sequential ids as primary keys for a table in your database?

Question

I don't know enough about databases to find the right words to ask this question, so let me give an example to explain what I'm trying to do: Suppose I want the primary key for a table to be an ID I grab from an API, but the majority of those API requests result in 404 errors. As a result, my table would look like this:

I also don't know how to format a table-like structure on Stack Overflow, so this is going to be a rough visual:

API_ID_PK | name
------------------
1         | Billy
5         | Timmy
23        | Richard
54        | Jobert
104       | Broccoli

Is it okay for the ID's not to be sequentially separated by 1 digit? Or should I do this:

ID PK |  API_ID   | NAME
----------------------------------------
1     |    1      | Billy
2     |    5      | Timmy
3     |    23     | Richard
4     |    54     | Jobert
5     |    104    | Broccoli

Would the second table be more efficient for indexing reasons? Or is the first table perfectly fine? Thanks!

Answer 1

No, there won't be any effect on efficiency if you have non-consecutive IDs. In fact, MySQL (and other databases) allow for you to set a variable auto_increment_increment to have the ID increment by more than 1. This is commonly used in multi-master setups.

Answer 2

It's fine to have IDs not sequential. I regularly use GUIDs for IDs when dealing with enterprise software where multiple business could share the same object and they're never sequential.

The one thing to watch out for is if the numbers are the same. What's determining the ID value you're storing?

Answer 3

If you have a clustered index (Sql-Server) on a ID column and insert IDs with random values (like Guids), this can have a negative effect, as the physical order of the clustered index corresponds to the logical order. This can lead to a lot of index re-organisations. See: Improving performance of cluster index GUID primary key.

However, ordered but non consecutive values (values not separated by 1) are not a problem for clustered indexes.

For non-clustered indexes the order doesn't matter. It is okay to insert random values for primary keys as long as they are unique.