SEO

Hello,

I have a sql script that create some tables and some of them have
foreign key restrictions like this:

alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF foreign
key (FK_SCHEDULE_ID)
references T_SCHEDULE (SCHEDULE_ID) on delete restrict on update
restrict;
alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF2 foreign
key (FK_DATAFLOW_ID)
references T_DATAFLOW (DATAFLOW_ID) on delete restrict on update
restrict;

I'm executing the same insert statement ('insert into
T_SCHEDULE_TO_DATAFLOW values (10,10,2);' and the 10 id is not valid)
in two diferent mysql databases (with the same sql script), one of
them is in the windows operative system and the other is in the linux.

Windows (XP) version of mysql is: mysql Ver 14.12 Distrib 5.0.27, for
win32
Linux (Suse server) version of mysql is: mysql Ver 14.12 Distrib
5.0.24a, for pc-linux-gnu (i686) using readline 5.0

And in windows i get what i'm was expecting:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint f
ails (`fxsim/t_schedule_to_dataflow`, CONSTRAINT `FK_T_SC_DF` FOREIGN
KEY (`FK_S
CHEDULE_ID`) REFERENCES `t_schedule` (`SCHEDULE_ID`))

But in the linux the insertion return ok, and does not return the
foreign key constraint error.

Any idea why this happen, or if there is any configuration to activate
this constraints and if exists what is...

Thanks
Nuno

On 13 Feb, 12:54, "Nuno" <nuno.escul...@gmail.com> wrote:
> Hello,
>
> I have a sql script that create some tables and some of them have
> foreign key restrictions like this:
>
> alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF foreign
> key (FK_SCHEDULE_ID)
> references T_SCHEDULE (SCHEDULE_ID) on delete restrict on update
> restrict;
> alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF2 foreign
> key (FK_DATAFLOW_ID)
> references T_DATAFLOW (DATAFLOW_ID) on delete restrict on update
> restrict;
>
> I'm executing the same insert statement ('insert into
> T_SCHEDULE_TO_DATAFLOW values (10,10,2);' and the 10 id is not valid)
> in two diferent mysql databases (with the same sql script), one of
> them is in the windows operative system and the other is in the linux.
>
> Windows (XP) version of mysql is: mysql Ver 14.12 Distrib 5.0.27, for
> win32
> Linux (Suse server) version of mysql is: mysql Ver 14.12 Distrib
> 5.0.24a, for pc-linux-gnu (i686) using readline 5.0
>
> And in windows i get what i'm was expecting:
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> constraint f
> ails (`fxsim/t_schedule_to_dataflow`, CONSTRAINT `FK_T_SC_DF` FOREIGN
> KEY (`FK_S
> CHEDULE_ID`) REFERENCES `t_schedule` (`SCHEDULE_ID`))
>
> But in the linux the insertion return ok, and does not return the
> foreign key constraint error.
>
> Any idea why this happen, or if there is any configuration to activate
> this constraints and if exists what is...
>
> Thanks
> Nuno

Apart from what the insert returns, on the linux system, do you see
the record in the table after the insert?

yes


Captain Paralytic escreveu:
> On 13 Feb, 12:54, "Nuno" <nuno.escul...@gmail.com> wrote:
> > Hello,
> >
> > I have a sql script that create some tables and some of them have
> > foreign key restrictions like this:
> >
> > alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF foreign
> > key (FK_SCHEDULE_ID)
> > references T_SCHEDULE (SCHEDULE_ID) on delete restrict on update
> > restrict;
> > alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF2 foreign
> > key (FK_DATAFLOW_ID)
> > references T_DATAFLOW (DATAFLOW_ID) on delete restrict on update
> > restrict;
> >
> > I'm executing the same insert statement ('insert into
> > T_SCHEDULE_TO_DATAFLOW values (10,10,2);' and the 10 id is not valid)
> > in two diferent mysql databases (with the same sql script), one of
> > them is in the windows operative system and the other is in the linux.
> >
> > Windows (XP) version of mysql is: mysql Ver 14.12 Distrib 5.0.27, for
> > win32
> > Linux (Suse server) version of mysql is: mysql Ver 14.12 Distrib
> > 5.0.24a, for pc-linux-gnu (i686) using readline 5.0
> >
> > And in windows i get what i'm was expecting:
> > ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> > constraint f
> > ails (`fxsim/t_schedule_to_dataflow`, CONSTRAINT `FK_T_SC_DF` FOREIGN
> > KEY (`FK_S
> > CHEDULE_ID`) REFERENCES `t_schedule` (`SCHEDULE_ID`))
> >
> > But in the linux the insertion return ok, and does not return the
> > foreign key constraint error.
> >
> > Any idea why this happen, or if there is any configuration to activate
> > this constraints and if exists what is...
> >
> > Thanks
> > Nuno
>
> Apart from what the insert returns, on the linux system, do you see
> the record in the table after the insert?

On 13 Feb, 15:00, "Nuno" <nuno.escul...@gmail.com> wrote:
> yes
>
> Captain Paralytic escreveu:
>
>
>
> > On 13 Feb, 12:54, "Nuno" <nuno.escul...@gmail.com> wrote:
> > > Hello,
>
> > > I have a sql script that create some tables and some of them have
> > > foreign key restrictions like this:
>
> > > alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF foreign
> > > key (FK_SCHEDULE_ID)
> > > references T_SCHEDULE (SCHEDULE_ID) on delete restrict on update
> > > restrict;
> > > alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF2 foreign
> > > key (FK_DATAFLOW_ID)
> > > references T_DATAFLOW (DATAFLOW_ID) on delete restrict on update
> > > restrict;
>
> > > I'm executing the same insert statement ('insert into
> > > T_SCHEDULE_TO_DATAFLOW values (10,10,2);' and the 10 id is not valid)
> > > in two diferent mysql databases (with the same sql script), one of
> > > them is in the windows operative system and the other is in the linux.
>
> > > Windows (XP) version of mysql is: mysql Ver 14.12 Distrib 5.0.27, for
> > > win32
> > > Linux (Suse server) version of mysql is: mysql Ver 14.12 Distrib
> > > 5.0.24a, for pc-linux-gnu (i686) using readline 5.0
>
> > > And in windows i get what i'm was expecting:
> > > ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> > > constraint f
> > > ails (`fxsim/t_schedule_to_dataflow`, CONSTRAINT `FK_T_SC_DF` FOREIGN
> > > KEY (`FK_S
> > > CHEDULE_ID`) REFERENCES `t_schedule` (`SCHEDULE_ID`))
>
> > > But in the linux the insertion return ok, and does not return the
> > > foreign key constraint error.
>
> > > Any idea why this happen, or if there is any configuration to activate
> > > this constraints and if exists what is...
>
> > > Thanks
> > > Nuno
>
> > Apart from what the insert returns, on the linux system, do you see
> > the record in the table after the insert?- Hide quoted text -
>
> - Show quoted text -

Are the tables in both systems using the same engine? Sounds like one
may be using InnoDB and the other MyISAM. MyISAM will accept the
foreign key ALTER commands but cannot implement them.