eve_mf eve_mf - 24 days ago 8
MySQL Question

how to check if foreign key exists in other table before adding a record

I know there are questions with answers that sound the same, but I have checked them, and I can't anything answering my question.

I have a recipes table, which has "chef_id" and "category_id" columns.
Then I have a chefs table, with "id" and "name" columns, and the categories table, with also "id" and "name" columns.

When I insert a new recipe and I have to add the chef_id and category_id, they need to exist already, otherwise, it should tell me to add the chef and the new category before I add the recipe.

How and where should I check that? I am working with SQL PDO. This is what I've got so far:

functions.php

function add_recipe($name =':name',
$categories_id=':categories_id',
$chef_id=':chef_id') {

include 'db_connection.php';
try {
$sql = "INSERT INTO recipes(name, categories_id, chef_id) "
. "VALUES (:name, :categories_id, :chef_id)";


if(chef_id and category_id exist) {
then execute the query;
}

$results = $conn->prepare($sql);
$results->bindParam(':name', $name, PDO::PARAM_STR, 100);
$results->bindParam(':categories_id', $categories_id, PDO::PARAM_INT);
$results->bindParam(':chef_id', $chef_id, PDO::PARAM_INT);

if($results->execute()) {
echo '1 row has been inserted';
}

$conn = null;

} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage() . '<br />';
return false;
}
return true;

}


The only thing I can think of being the right one is SQL CHECK Constraint.... but I hope it is not this because I am finding quiet difficult to understand how it works...

Thank you!

Answer

You create a store procedure in MySql instead, to avoid multiple round trips to check db

Im going to use pseudo code, because in a phone right now :

  CREATE FUNCTION create_receipt(p_name, p_chef, p_category) 
  {
     int i_chef_id
     int i_category_id

     -- try to get chef_id if exist
     SELECT INTO i_chef_id
                 chef_id
     FROM chefs
     WHERE chef = p_chef

     if i_chef_id IS NULL {
        -- create new chef and get new created id
        INSERT chef  values (p_chef)

        SELECT INTO i_chef_id
                    chef_id
        FROM chefs
        WHERE chef = p_chef         
     }

     if not exist category {
        INSERT category values(category)
     }

     INSERT receipt  (name, i_chef_id, i_category_id) 
  }
Comments