locked
mySql database stops working when I add a Foreign Key RRS feed

  • Question

  • User-509601008 posted

    My website and database work fine in WebMatrix until I add a Foreign Key, upon which I get the error message listed below.  I created the connections in MySql Workbench, so I know that the problem isn't human error on this one.

    Is there something I need to be doing differently in order to create the relationships between tables when working with mySql in WebMatrix and/or .Net?

    Server Error in '/' Application.


    Cannot add or update a child row: a foreign key constraint fails (`c2f_122011`.`flatter1`, CONSTRAINT `fk_flatter1_customer_info11` FOREIGN KEY (`customer_info1_user_id`) REFERENCES `customer_info1` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

    Tuesday, December 20, 2011 5:39 PM

All replies

  • User1501365188 posted

    first of all u remove all the record and then add foreign key this will work 

    this error occur beacase of data dose not match like primary key and foreign key value.

    Tuesday, December 20, 2011 11:18 PM
  • User-509601008 posted

    I've already removed all the records, so unfortunately that's not the problem

    Wednesday, December 21, 2011 1:33 AM
  • User-1407477457 posted

    It sounds like you are attempting to write to one table without ensuring that a matching record exists in the foreign key table first.  There are lots of ways to deal with this.  The best one for you depends on the needs of your application.

    Wednesday, December 21, 2011 8:08 AM
  • User-509601008 posted

    I am creating in-depth customer profiles with the main table (customer_info1) being where the customer account and general information is stored, and it connects to several tables which each have specific types of customer information (i.e. 'fit' has their measurements and other information that relates to garment fit). Each table's information has to connect to user_id on the customer_info1 table in order for me to pull up information by customer.

    Each table generates an auto-increment id (which is working properly), and when I've tested it I've gone to the main page first in order to create the user_id which the other tables all refer to.

    The specific settings (in MySqlWorkbench) for table 'fit1' are:

    Relevant Columns

    fit_id - INT(11), Primary Key, Not Null, Unique Index, Auto Incremental

    customer_info1_user_id - INT(11), Primary Key, Not Null, Auto Incremental

    Indexes

    Primary -Type = Primary; Index Columns=fit_id and customer_info1_user_id; #=1 and 2

    idstyle_UNIQUE - Type=Unique; Index Columns=fit_id; #=1

    fk_fit1_customer_info1 - Type = Index; Index Columns=customer_info1_user_id; #=1

    Foreign Keys

    fk_fit1_customer_info11 - Referenced Table 'c2f_122011'.'customer_info1'; Column=customer_info1_user_id; Referenced Column=user_id

    Wednesday, December 21, 2011 10:38 AM
  • User-1407477457 posted
    You mention both tables are autoincrement. That's fine. If you add a new record to table customer_info1, how are you currently getting the value of the user_id field to use with the fit1 table?
    Wednesday, December 21, 2011 12:21 PM
  • User-509601008 posted

    I was under the impresssion that the Foreigh Key fk_fit1_customer_info11 created the connection between customer_info1_user_id in the fit1 table and the user_id in the customer_info1 table.

    PRIMARY KEY (`fit_id`, `customer_info1_user_id`) ,
      UNIQUE INDEX `customer_id_UNIQUE` (`fit_id` ASC) ,
      INDEX `fk_fit1_customer_info1` (`customer_info1_user_id` ASC) ,
      CONSTRAINT `fk_fit1_customer_info1`
        FOREIGN KEY (`customer_info1_user_id` )
        REFERENCES `c2f_122011`.`customer_info1` (`user_id` )
        ON DELETE CASCADE
        ON UPDATE CASCADE

    I've also tried setting ON DELETE and ON UPDATE to NO ACTION, but I still got the same error message.

    Is there something else that I'm supposed to be doing?

    Wednesday, December 21, 2011 2:21 PM
  • User-1407477457 posted

    The foreign key creates a constraint in that values in the fit1 table have to exist in the customer_user1 table. 

    It does not do anything to get a value from the customer_user1 table that you can use in an insert or update query.  You have to do that yourself.

    Wednesday, December 21, 2011 2:47 PM
  • User-509601008 posted

    No wonder it isn't working :-)

    Since reading your posting I've been trying to figure out how to set up  LAST_INSERT_ID() but I'm having a tough time figuring out how to apply it to my code to get it to work because most of the instructions I've found are for PHP or are too technical in nature :-(

    Would you be able to tell me how to do it for the code snippet below?

      @{
         var clothingsize="";

           if(IsPost){     
         clothingsize =Request["clothingsize"];         

      var SQLINSERT = "INSERT INTO fit1 (clothingsize) VALUES (@0)";

             var db = Database.Open("Demo1");

             db.Execute(SQLINSERT, clothingsize);
            Response.Redirect("flatter.cshtml");

      }
         }

    Wednesday, December 21, 2011 7:30 PM