I had mysql database and had many tables in database. For this problem we need details only about two tables. These tables are customer and guarantor. For each customer had two guarantors.
In Customer tables it's primary key is Customer_id
In Guarantor table it's primary key is Guarantor_id
Then I think you need details about software side. I develop software with vb.net. For new customer it has new customer window. In this form there are two list boxes to select two guarantors. And when select gaurantor from list box, some details like name, address are loaded to this form.
Again database side,
In customer table I already said that customer_id is primary key. And guarantor1_name, guarantor2_name are two foreign keys. Note that guarantor_name in guarantor table is reference table and coulmn for cusomer table's guarantor1_name, guarantor2_name.
(I don't know whether is it right or wrong. please correct me if I'm wrong. Because I'm new)
When I'm trying to add customer by new customer form there is no error. Make sure this time I'm only select guarantors name from listbox.
But there is option if guarantor not exist in list add new one by clicking list box 1st item called "Add new " When I click add new there is new form open called New gaurantor. As normally fill text boxes and click 'save & close' new form add this guarantor to database and it's data passed to new customer form.
Think I'm added gurantors like this way. Then after I fill all the text boxes in new customer and click Save button the error happen.
Error saying that,
"Cannot add or update a child row: a foreign key constrant fails ('dbname/customer',constrait 'FK_customer_1' FOREIGN KEY ('Guarantor_1_Full_Name') REFERENCES' guarantor ' ('Guarantor_name') ON UPDATE CASCADE)
Why do you reference the guarantor by name, and not by ID?
If your database already opened before and after saving new garantor you can get this error because the new one is not loded in memory.
After saving new garantor close the connection and re-open it before sving the new customer.
Now my problem solved. if two columns equal then there is no problem.
@pritease because if guarantor name changed I want to update customers guarantor name column .
Thanks !
Ok, that's why linking by ID is the common way to do this. Then no update is required, just a change in your query.
Agree with the ID linking)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.