Any drawbacks to starting an ID column of a table with 0?

user_drug.user_id is constrained by Foreign Key drug.id (which is a Primary Key).

Table structure is as follows:

user
id    name  income
1     Foo   10000
2     Bar   20000
3     Baz   30000

drug
id    name
0     Marijuana
1     Cocaine
2     Heroin

user_drug
user_id drug_id
1       1
1       2
2 开发者_StackOverflow      1       
2       3
3       3

Are there any drawbacks to starting drug.id at 0? I have a feeling that that will make things more natural with PHP since arrays also start at 0, but I want to make sure there aren't any drawbacks with using '0' for an id (e.g. it might be interpreted as null or some other strange potential occurrence/conflict).

If its an AUTO INCREMENT col, zero is a special value which shouldnt be used, but this can be overcome. If its a foreign key and non AUTO INCREMENT, any value is OK. Zero will not be interpreted as anything other than a zero if your column definition is correct (null values appear as NULL, which is distinct from zero)

It should be OK if that's not auto increment, but I wouldn't use it. 0 is the defaut value used by MySQL when the column can't be NULL (and it's an INT column).

I don't think there are any drawback but you need to make sure if you check the id that 0 is not interpretated as false.

It will not make things more natural since it's arbitrary, your code should not care what the ID is (i.e. you never see it).

Suppose someone is searching for an id, you then do intval($_GET['name']) on the value (to make sure you don't get an SQL injection).

If it's legal for the value to be 0 you will not be able to distinguish between "missing" AKA blank string, and an actual 0. Obviously you can work around that: You can check for a blank string, or you can use -1 to indicate missing.

But using 0 is easier.

Personally, I've used -1 to indicate missing (and allow 0), but since MySQL always defaults to starting with 1 I've started to get a bit lazy and just accept it.