AutoIncrements in SQL

[gemma-the-husky]

Can you have an autoincrement field value of 0 in a SQL table

In Jet, I sometimes manually insert a 0 index value to autonumber fields - to enable me to have a (default) value of 0 in the lookup field of the main table

I get the feeling that SQL doesn't like this work round, as it wouldn't transfer records for tables with a 0 item, in the autonumber field.

 

[DCrake]

Do you know? well definately not, I did not know you could start at 0. I knew you could force a number in the autonumber field but did not realise you could start at 0. Thanks for that Dave.

Mind you I have never tried to do it.

 

[SQL_Hell]

It's fine to have an identity column start at zero, we do it all the time here

 

[Banana]

Be careful about which backend you're doing this with. Giving 0 to MySQL will return the incremented value, IIRC.

I would be wary toward messing with the autoincrementing behavior because this is one of least portable aspect of SQL.

 

[gemma-the-husky]

I was trying to upsize some Jet tables into MySQL, and 2 tables would not migrate. I got error messages about unique keys - I think these related to Jet tables where I had forced in a 0 key value, to help my RI.

So these observations make sense, thanks

 

[SQL_Hell]

Sorry, I thought this was a MSSQL question not MYSQL

 

[Banana]

Technically, MySQL questions shouldn't be in SQL Server forum - I think we have a MySQL forum somewhere (should be "PHP, Perl & MySQL" or at least under the web development)