CODEWITHSUNDEEP
phpmysql
user3420034
user3420034

Reputation:

Does a MySQL table require an auto-incrementing ID?

So normally when I create new tables I'd have a column called id set to the primary and set it to auto-increment.

In a new project I'm working on, for each row I need a unique id of a specific length, 15 characters in this case.

So my question, is there an issue with using this 15 character numerical id in place of an auto-incrementing id? Is there a technical reason for using an auto-incrementing number?

I use this to generate the IDs, so as far as I'm aware they're guaranteed to be unique:

$randNum = preg_replace('/[.:]/', '', time().$_SERVER['REMOTE_ADDR']).randomNumber(4)

function randomNumber($length) {
    $result = '';
    for($i = 0; $i < $length; $i++) {
        $result .= mt_rand(0, 9);
    }
    return $result;
}

Better algorithm?

$randNum = round(microtime(true) * 1000).randomNumber(7)

function randomNumber($length) {
    $result = '';
    for($i = 0; $i < $length; $i++) {
        $result .= mt_rand(0, 9);
    }
    return $result;
}

Upvotes: 2

Views: 688

Answers (4)

Tim
Tim

Reputation: 2163

No, it is not required by MySQL to have an auto-incrementing ID column. However, as noted in the manual, an index key is essential to good database design. If you're manually generating the ID instead of using auto-increment, that's fine, but make sure that the algorithm you use to generate IDs always generates unique IDs (which is not the case with algorithm you posted).

Upvotes: 3

Jesse Weigert
Jesse Weigert

Reputation: 4854

In general, every table in your database should have a primary key. It can either be an auto incrementing id column or a unique identifier that naturally occurs in your data, or some other method of generating a unique identifier (such as a guid)

MySQL doesn't actually require a primary key at all, but omitting one is generally a very bad idea.

Upvotes: 0

Rahul
Rahul

Reputation: 77876

Is there a technical reason for using an auto-incrementing number?

Auto Increment ID are also known as synthetic key columns which are auto generated by the system and make sure that they are unique. It's not mandatory to have auto ID as primary key. You can define a column as primary key with your own key values unless they make sure to be unique.

for each row I need a unique id of a specific length, 15 characters in this case.

In this case, don't define it to be AUTO INCREMENT and make it a VARCHAR(15) column and say for example if you are populating data from your application and say for example it's a .NET/C# application you can use GUID as that primary key (OR) some other key to that matter.

Upvotes: 2

Coder-guy
Coder-guy

Reputation: 424

There is no need to have an auto-incremented id in your table. You can use the id you are creating as the primary key. As for your randomNumber() function: as I'm sure you know, there is no guarantee that the numbers will be different, just a very very good chance. If you want to use it, you should add a check to see if that number exists in your database already

Upvotes: 0

Related Questions