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.

Answer 1

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:

• Add Foreign Key to existing table
• http://dev.mysql.com/doc/refman/5.0/en/alter-table.html