[thelist] make mysql accept 0 (zero) as a primary key value

Sarah Sweeney mr.sanders at designshift.com
Thu Dec 9 12:22:49 CST 2004

I've got a database which contains all of the data for a client's 
siteand custom CMS. When I'm doing development for the site, I do it on 
a local machine with a local copy of the database. I like to keep this 
copy of the database fairly current by doing regular dumps of the live 
database and then running the dump file on the local database.

For the most part, this works fine, with just one exception. I have a 
lookup table called Agencies, containing columns AgencyID and 
AgencyName. I've added a dummy row to the database with AgencyID = 0 and 
AgencyName = 'No Agency'; this allows me to avoid doing a lot of left 
joins in queries throughout the site, and to have the value hard-coded 
in several places. If I do a dump of the entire database, then try to 
run the dump file in my local copy of the database, I get the error 
"Duplicate entry '1' for key 1" - basically, it will add the row with 
the AgencyID = 0 fine, but fails when it then tries to add the row with 
AgencyID = 1. I can go in and manually add the row, just not using the 
dump file.

Is there some option I can add to the create table definition that will 
make this work, or a database-wide setting that will work? Any 
suggestions on how to fix this problem will be greatly appreciated.

Sarah Sweeney  ::  Web Developer & Programmer
Portfolio  ::  http://sarah.designshift.com
Blog  ::  http://hardedge.ca

More information about the thelist mailing list