Inserting into mySQL table with a foreign key that hasn't been created yet.

Question

Okay I have two tables. They both have keys. I have another table that references both keys. How can I make a constraint when I add into that third if either of the keys don't exist in the first two tables it won't insert that tuple into the third table. I am inserting in phpMyAdmin. Thanks.

Answer 1

EDIT:

Sorry for misreading your question. Mysql only supports contraints with certain engines. InnoDB is the one i know of and generally use, but other might im really not sure. So with inno DB youd need th following schema:

CREATE TABLE my_table_a (
  `id` INTEGER UNSIGNED NOT NULL auto_increment,
   # your other column definitions
  PRIMARY KEY (`id`)
) ENGINE=InnoDB; 

CREATE TABLE my_table_b (
  `id` INTEGER UNSIGNED NOT NULL auto_increment,
   # your other column definitions
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE my_table_a_b (
  `id` INTEGER UNSIGNED NOT NULL auto_increment,
  `table_a_id` INTEGER UNSIGNED NOT NULL,
  `table_b_id` INTEGER UNSIGNED NOT NULL
  PRIMARY KEY (`id`),
  KEY (`table_a_id`),
  KEY (`table_b_id`),
  CONSTRAINT `ab_FK_a`
      FOREIGN KEY (`table_a_id`)
      REFERENCES `my_table_a` (`id`),
  CONSTRAINT `ab_FK_b`
      FOREIGN KEY (`table_b_id`)
      REFERENCES `my_table_b` (`id`)
) ENGINE=InnoDB;

---

SET FOREIGN_KEY_CHECKS=0;

# Your INSERTS

SET FOREIGN_KEY_CHECKS=1;

This will disable the FK checks ast the sql statements reflect until after you have performed your inserts. If i remember correctly It will then check the keys once turned back on. Because of this you may want to use a transaction so that you can roll back if you have made a mistake and the keys dont match up in the end. Otherwise you might have partial data which is never fun :-)

Answer 2

i don't know about constraint but you may use this insert query:
INSERT INTO TAB3(TAB3_key1,TAB3_key2) SELECT val1,val2 FROM TAB1 JOIN TAB2 ON EXISTS (SELECT * FROM TAB1 JOIN TAB2 ON TAB1_key=val1 AND TAB2_key=val2) LIMIT 1;

where val1,val2 - values that you want to insert.