Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    3

    Red face Unanswered: Foreign key problem

    Hi All,
    I have a table "MASTER" with primary key "acno".
    and another table "TendTrans" with primary key "acno,TDate".

    i am writing below query as

    Alter table TendTrans add constraint foreign key (acno) references master(acno) on delete cascade;


    it working perfectly.

    but, whenever my server is restarted this foreign key constraint is failing and not showing.
    again i have to write this alter command..

    can any one pls tell me the remedy.......


    thanks in advance.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am not sure what you mean by "failing and not showing". Does it fail and return an error?

    Also when you say your server is restarted is this the database server or the host server? Is the database being closed properly during the shutdown?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    May 2012
    Posts
    3

    Smile

    Hi Ronan,
    Thanks for ur Reply Post,

    i am using database server and host server in a single system.
    and there need to be restart my system every day..

    if system is restarted and i want to delete master record, then giving error as

    " Cannot delete a parent row: a foreign key constraint fails "

    may i know that, is there any database proper shutdown script ???
    if s, then pls let me know that.

    regards,

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Are you on a Windows or Linux system?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    May 2012
    Posts
    3
    Windows System.......

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Not really sure about this. During the installation process it should have created a service that auto starts and shuts down but I am not too sure. If you can stop the service manually first then reboot and see if that solves the problem then you know that this is where the issue is.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20

    Bad data in tables? foreign_key_checks?

    This error almost always comes from bad data in the tables. It's strange that it seems to only happen when the server starts up (and it doesn't catch it when you actually create the foreign key). The only thing I can think of is that somehow foreign_key_checks is getting turned off before you create it, and then when the server restarts it finds the bad data, and kills the foreign key.

    Have you checked the data in there? This article has a good query for checking for bad data (along with a lot of other causes of MySQL foreign key errors). Check out the very first item in the list under "Most Common Reasons for MySQL Foreign Key Errors".
    MySQL Foreign Key Errors and Errno: 150

    When you go to create the foreign key, you can also check to make sure foreign_key_checks is on:
    SELECT @@FOREIGN_KEY_CHECKS;
    If it's not 1, then it's not checking data before creating the foreign key.

  8. #8
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20
    I just reread your post, and I think that I misread it the first time. Are you saying that after you restart the server, it changes the foreign key from ON DELETE CASCADE to ON DELETE RESTRICT?

    Based on what you said, it sounds like the foreign key is still there, but after you restart it won't cascade the delete?

Posting Permissions

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