[RESOLVED] primary key question

182x

Hey guys, does the primary key in MySQL always start and '1' and if so is this the same in oracle?

Thanks.

laserlight

Hey guys, does the primary key in MySQL always start and '1' and if so is this the same in oracle?

No, since primary keys need not be a number.

182x

laserlight wrote:
No, since primary keys need not be a number.

Sorry I mean when using a number or is there a chance they could start at 0.

laserlight

is there a chance they could start at 0

MySQL's auto incremented integer keys start at 1 unless you specify otherwise. I do not know about Oracle, but generally it should not make a difference whether it starts at 0, 1 or 1001.

MarkR

In MySQL, primary keys do not need to be a single column or a number. However, they often are.

auto increment columns need not be primary keys and can start at any value you want. This can be controlled by appropriate ALTER TABLE statements, for example.

Normally I prefer not to use zero as a valid ID value, as it makes it more difficult to distinguish an invalid and valid value.

Mark

tomhath

MySql treats zero as a magic value in auto-increment columns. Either avoid it or make sure you understand what can happen with different "modes":

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

An integer column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.
...
As of MySQL 4.1.1, if the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 5.2.6, “SQL Modes”.

MarkR

I think the bottom line is, avoid zero as an ID, whether it's auto numbered or not.

Mark

182x

Thank you for all the replies. 🙂