We help IT Professionals succeed at work.

Auto_Increment to begin with 0

SnowFlake
SnowFlake asked
on
1,087 Views
Last Modified: 2012-06-21
Hi,
the short version:
How do I make it so the first value assigned to an auto_increment field will be 0 ?

the long version:
I am trying to make the ASP.Net portal starter kit work with MySql
(i.e. porting it from MSSQL to MySql),
so I will be able to add discussions and other portal features to my site
at http://JSBugHunter.Com.

I have finished most of the proccess but not I have the following problem:

when loading the initial data I have the line
INSERT INTO Portal_UserRoles (UserID,RoleID) VALUES (1,0);

when running them I get "
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`portal/portal_userroles`, CONSTRAINT `FK_UserRoles_Roles` FOREIGN KEY (`R
oleID`) REFERENCES `portal_roles` (`RoleID`) ON DELETE CASCADE)
"
The two lines that execute before that are:
INSERT INTO Portal_Roles (PortalID,RoleName) VALUES (0,'Admins');
INSERT INTO Portal_Users (Name, Password, Email) VALUES ('Guest','D0-09-1A-0F-E2-B2-09-34-D8-8B-46-06-84-F5-97-89','guest');

but as you can see
mysql> select * from Portal_Roles;
+--------+----------+----------+
| RoleID | PortalID | RoleName |
+--------+----------+----------+
|      1 |        0 | Admins   |
+--------+----------+----------+

the Admins role is created with RoleID 1 and not 0 as seems to be expected by
INSERT INTO Portal_UserRoles (UserID,RoleID) VALUES (1,0);


the Portal_Roles table is created using:
CREATE TABLE `Portal_Roles`(
RoleID int NOT NULL auto_increment,
PortalID int NOT NULL,
RoleName nvarchar (50) NOT NULL,
PRIMARY KEY  (`RoleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

but when I do show create table for it it does not show the Auto_Increment=0 option.
the original create table (MSSQL) was
CREATE TABLE [dbo].[Portal_Roles] (
  [RoleID] [int] IDENTITY (0, 1) NOT NULL ,
  [PortalID] [int] NOT NULL ,
  [RoleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

which as you can see sets the ROLEID to begin with a value of 0;

I also tried to
set global sql_mode='NO_AUTO_VALUE_ON_ZERO';

but either I am doing it wrong or it only means that if I do and implicit insert of a 0 it will work.


Thanks,
SnowFlake
Comment
Watch Question

Top Expert 2006

Commented:
The internal logic uses (last auto_increment value + 1) to assign the new auto increment value.  When you set it to 0, the first row entered will be given an id of 1.  You can't set the AUTO_INCREMENT to -1 (at least, you can't in my verson, 5.0.18) so that the first row is 0.

In short, I don't think it is possible to have the auto increment counter start with zero.

Author

Commented:
well,
I don't know if your answer is right, it seems to be right but it just feels so not appropriate
I wander what other people that are porting such systems do.

I ended up setting the SQLMode to 'NO_AUTO_VALUE_ON_ZERO'
and changed the Initial DataLoad SQL statements to explicitly assign the 0 to my Auto_increment fields

so for example
   INSERT INTO Portal_Links (ModuleID) VALUES (0);
was changed into
   INSERT INTO Portal_Links (ItemId,ModuleID) VALUES (0,0);

where ItemId is the Auto_Increment field.

I will leave the Q open for a while just in case someone has something to add to this.

SnowFlake
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
although somwhat furstrating,
It is probably the "sad" reality.

anyway for my own needs I ended up with the solution I described above.

Thanks,
SnowFlake
Top Expert 2006

Commented:
Yeah - I was hoping somebody else had a way to make it happen.  Thanks for the points!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.