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)