Link to home
Start Free TrialLog in
Avatar of SnowFlake
SnowFlakeFlag for Israel

asked on

Auto_Increment to begin with 0

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
Avatar of todd_farmer
todd_farmer
Flag of United States of America image

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.
Avatar of SnowFlake

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of todd_farmer
todd_farmer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Yeah - I was hoping somebody else had a way to make it happen.  Thanks for the points!