SnowFlake
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-B 2-09-34-D8 -8B-46-06- 84-F5-97-8 9','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
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`
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-B
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_
) 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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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!
In short, I don't think it is possible to have the auto increment counter start with zero.