implementing referntial integrity with php mysqli

Question

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.

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.) — ZZ-bb, Jun 24 '15 at 06: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".. — user3872868, Jun 24 '15 at 07: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. — ZZ-bb, Jun 24 '15 at 07: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. — ZZ-bb, Jun 24 '15 at 07:22
Transaction is explained for example here (just a simple example): http://stackoverflow.com/questions/10043887/sql-insert-into-multiple-tables/10044742#10044742 — ZZ-bb, Jun 24 '15 at 07:31
following ur seggestion i tried : ALTER TABLE contacts ADD FOREIGN KEY (c_id) REFERENCES pers_info(id); but it gave error : Cannot add or update a child row: a foreign key constraint fails (`family`.`#sql-12bc_5d`, CONSTRAINT `#sql-12bc_5d_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `pers_info` (`id`)) — user3872868, Jun 24 '15 at 07:38
Check your syntax. You `ADD CONSTRAINT`: http://stackoverflow.com/questions/10028214/add-foreign-key-to-existing-table, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html — ZZ-bb, Jun 24 '15 at 08:02

score 0 · Accepted Answer · edited May 23 '17 at 11:51

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:

implementing referntial integrity with php mysqli

1 Answers1