Professional Web Applications Themes

Foreign key constraint fails - MySQL

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. ...

  1. #1

    Default Foreign key constraint fails

    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

    Nuno Guest

  2. #2

    Default Re: Foreign key constraint fails

    On 13 Feb, 12:54, "Nuno" <com> wrote: 

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

    Captain Guest

  3. #3

    Default Re: Foreign key constraint fails

    yes


    Captain Paralytic escreveu: 
    >
    > Apart from what the insert returns, on the linux system, do you see
    > the record in the table after the insert?[/ref]

    Nuno Guest

  4. #4

    Default Re: Foreign key constraint fails

    On 13 Feb, 15:00, "Nuno" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > - Show quoted text -[/ref]

    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.

    Captain Guest

Similar Threads

  1. Replies: 0
    Last Post: September 26th, 05:35 AM
  2. Replies: 11
    Last Post: June 2nd, 04:01 AM
  3. Adding Constraint
    By wallace reis in forum MySQL
    Replies: 2
    Last Post: December 15th, 07:27 PM
  4. Space used to store a foreign key constraint
    By Jos in forum Oracle Server
    Replies: 4
    Last Post: October 27th, 08:04 AM
  5. Can Not Access Foreign Key Constraint
    By Rajesh Tiwari in forum ASP.NET General
    Replies: 0
    Last Post: June 30th, 02:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139