CODEWITHSUNDEEP
mysqlsqldatabase
A.Vinuela
A.Vinuela

Reputation: 288

Why SQL primary key index begin at 1 and not at 0?

I am wondering, why does the SQL primary key index begin at 1, and not at 0? Is there a reason?

I don't know if this is the good place to ask a question like this, but found no answer on the web.

Upvotes: 16

Views: 15883

Answers (3)

P.Salmon
P.Salmon

Reputation: 17655

The sql standard 4.15.11 Identity columns - https://www.sis.se/api/document/preview/916885/ says 'an identity column has a start value an end value an increment and a cycle option' it doesn't say that it should start at any specific number but since it's purpose is the provision of a unique row number (if set as primary key) then a negative start value would be meaningless. MYSQL will allow you to set the start value to 0 if you wish but I don't see the point. In my view allowing array subscripts to start from 0 in other languages was always daft and mysql having a limit offset clause which starts at 0 is an annoying inconsistency.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Counting in SQL generally starts as "1". For instance, the substring operations count characters in a string from 1 (and not 0). row_number() enumerates rows in a group, starting from 1 (and not 0).

The reason for this is simple. Humans start counting at 1 -- just ask many four-year olds. We don't start counting at zero until we learn programming. As a species, we counted from "1" for many millennia before adding zero to the pantheon of numbers.

Counting from 1 is much more intuitive when you are counting rows from tables: "1" for the first row requires little explanation, in particular for people who are not programmers. "0" for the first row requires explanation.

It is easy to forget, but SQL was devised for non-programmers. Much of the verbosity is because it was intended for business users.

Upvotes: 23

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use this SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO' then it will start from 0

mysql docs

Upvotes: 3

Related Questions