1

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       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).

ProgrammerGirl
  • 3,157
  • 7
  • 45
  • 82

4 Answers4

4

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)

Community
  • 1
  • 1
SW4
  • 69,876
  • 20
  • 132
  • 137
  • In this case, would you recommend starting the ID at 0 or 1? – ProgrammerGirl Oct 06 '11 at 10:03
  • 1
    If the column isnt an AUTO INCREMENT (AI) field then it doenst really matter, however if it is linked to a field which is (e.g. if it is the FK (foreign key) to a field in another table where its the PK and AI), it should be set to 1, unless you have made the changes to your SQL implementation as stated above. I would tend to get in the habit of using 1 and not zero for SQL, and zero not 1 for array positions in PHP...that way you always know where you stand. – SW4 Oct 06 '11 at 10:05
  • 1
    To go back to your question, a 'zero' entry will only be read as a zero (not null, empy string, false etc), so as far as actually reading the entry, there will be no conflict. – SW4 Oct 06 '11 at 10:09
2

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).

Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261
0

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.

Tobias
  • 9,170
  • 3
  • 24
  • 30
-1

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.

Ariel
  • 25,995
  • 5
  • 59
  • 69
  • -1 for the pointless example. what's wrong with intval($_GET['name'])? – Your Common Sense Oct 06 '11 at 10:11
  • @Col.Shrapnel Did you not read what I wrote? It's in the very next paragraph. – Ariel Oct 06 '11 at 10:18
  • this is one-to-many relationship. "missing" relation is just represented by missing row – Your Common Sense Oct 06 '11 at 11:05
  • @Col.Shrapnel I'm quite surprised at you. You have lots of karma, but can't seem to manage to read what I wrote. Are you in a hurry and are skimming? I'm talking about a WEB search for a value! A missing value in the WEB search, not in the database. i.e. I'm doing a search, but I don't want to search using that column. – Ariel Oct 06 '11 at 11:34
  • what does a web search do with numeric identifier? – Your Common Sense Oct 06 '11 at 11:39
  • I have a select list with records, I highlight the one I want to search for, or I highlight none. The web search transmits the ID of the record I selected. Seriously, this is elementary stuff. – Ariel Oct 06 '11 at 11:55