i have three tabs in my Db:
1.pers_info(id(primary), name ,....) 2.contacts(c_id(primary), phone, email, ...) now 1 person can have multiple rows in contacts tab. thus to minimize redundancy i made another tab contact_relation(id (foregin key references pers_info(id), c_id (foregin key references contacts(c_id))
i successfully created the relation and also was able to insert apt entries(related id and c_id) to it "using last_insert_id();" to extract id and c_id required.
now the problem..
i dropped tab contacts_rel. and created it this way :
CREATE TABLE contacts_relation ( id INT NOT NULL, cid INT NOT NULL, FOREIGN KEY (id) REFERENCES pers_info(id) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (c_id) REFERENCES contacts(c_id) ); if now i try to insert into tab cont_rel, it gives error : cannot add or update a child row foreign key constraint fails. which makes sense..
i was hoping that adding constraints in the defn of tab cont_rel would save the hassle of inserting entries in it manually using "last_insert_id();"
so is there a way, i could maintain ref integrity with new data coming.. thanks.
contactsneed FK to tablepers_info. Add it if you don't have already. It doesn's show up in your compact table info.)pers_info) and the add to the child table (contancts). You useLAST_INSERT_ID()to add the FK to the child table (if you add to two tables at the same time). You can also add only to the child table but you need to know theidof the person from the mother table.