CODEWITHSUNDEEP
mysqlinsert
learner
learner

Reputation: 4818

Entering data in multiple tables using foreign key constraints

I have following data:

Array
(
    [teacher_street] => Okhla
    [teacher_city] => Delhi
    [teacher_state] => National Capital Territory of Delhi
    [teacher_pin] => 110025
)

I need to insert the above data into a table called address:

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL,
  `address_street` varchar(255) NOT NULL,
  `city_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `address_pin` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now, I need to insert city and state in city table and state table first, if they don't exist. If they exist, I need to get primary keys corresponding to them:

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL,
  `city_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `state` (
  `state_id` int(11) NOT NULL,
  `state_name` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After that I need to get the corresponding primary keys and need to put in first table. How can I achieve the same in one go?

Upvotes: 1

Views: 24

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

Your city and state tables appear to have no AUTO_INCREMENT primary keys. You would have to specify the value as you insert into these tables.

If so, then your application code has the value it just inserted, and you can use it as you insert to the address table.

If you do in fact use AUTO_INCREMENT primary keys, you'll have to query LAST_INSERT_ID() to fetch the id after you insert to the city and state tables, and use these as you insert to the address table. Or else if you don't want to do that, query the id by searching for city name or state name before you insert to address.

Upvotes: 1

Related Questions