0

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.

8
  • Why the third table? When you many to many relation you need intermediate table. You have one to many relation so two tables are sufficient. Or am I missing something? (Table contacts need FK to table pers_info. Add it if you don't have already. It doesn's show up in your compact table info.) Commented Jun 24, 2015 at 6:56
  • i agree @ZZ-bb, thanks for ur comment, but even following ur way would i be able to insert into table contacts(having c_id as FK , referencing id of pers_info.. wont it sill give the same error : "cannot add or update a child row foreign key constraint fails".. Commented Jun 24, 2015 at 7:11
  • I'm not sure if I understand you but no. You add (inside transaction) first to the mother table (pers_info) and the add to the child table (contancts). You use LAST_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 the id of the person from the mother table. Commented Jun 24, 2015 at 7:20
  • BTW: you can also allow your FK to be null but that's kind of silly thing to do since you won't be able to connect that contact info to any person. But at least you won't get errors. But this is why errors are a good thing. If your FK constraint fail, it's good to know about it. Commented Jun 24, 2015 at 7:22
  • Transaction is explained for example here (just a simple example): stackoverflow.com/questions/10043887/… Commented Jun 24, 2015 at 7:31

1 Answer 1

0

Why the third table? When you many to many relation you need intermediate table. You have one to many relation so two tables are sufficient. Table contacts need FK to table pers_info. Add it if you don't have already.

Here's a simple transaction example with LAST_INSERT_ID(): SQL INSERT INTO multiple tables

Some info about foreign keys and altering table:

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.