Insert Data into Tables Linked by Foreign Key

Insert Data Into Tables Linked by Foreign Key

You can do it in one sql statement for existing customers, 3 statements for new ones. All you have to do is be an optimist and act as though the customer already exists:

insert into "order" (customer_id, price) values \
((select customer_id from customer where name = 'John'), 12.34);

If the customer does not exist, you'll get an sql exception which text will be something like:

null value in column "customer_id" violates not-null constraint

(providing you made customer_id non-nullable, which I'm sure you did). When that exception occurs, insert the customer into the customer table and redo the insert into the order table:

insert into customer(name) values ('John');
insert into "order" (customer_id, price) values \
((select customer_id from customer where name = 'John'), 12.34);

Unless your business is growing at a rate that will make "where to put all the money" your only real problem, most of your inserts will be for existing customers. So, most of the time, the exception won't occur and you'll be done in one statement.

Insert data into a table with a foreign key SQL

Is there any specific reason to not use integer IDs? I would do the following:

CREATE TABLE Item (
Id INTEGER NOT NULL IDENTITY PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
itemLink VARCHAR(100) NOT NULL,
description VARCHAR(1000) NOT NULL,
);

CREATE TABLE ItemBook (
Id INTEGER NOT NULL IDENTITY PRIMARY KEY,
ItemName VARCHAR(100) NOT NULL,
Publisher VARCHAR(100) NOT NULL,
ItemId INTEGER NOT NULL,
FOREIGN KEY (ItemId) REFERENCES Item(Id)
);

INSERT INTO ItemBook Values(1, 'ItemName', 'Publisher', 0)

What are your thoughts?

Edit 1. Based on your response and example, I have produced the following SQL for SQLITE (should work fine in other DBs as well)

CREATE TABLE Item (
Name VARCHAR(100) NOT NULL,
ItemLink VARCHAR(100) NOT NULL,
Description VARCHAR(1000) NOT NULL,
PRIMARY KEY (Name)
);

CREATE TABLE ItemBook (
ItemName VARCHAR(100) NOT NULL,
Publisher VARCHAR(100) NOT NULL,
PRIMARY KEY (ItemName),
FOREIGN KEY (ItemName) REFERENCES Item(Name)
);

INSERT INTO Item (Name, ItemLink, Description) VALUES("Test Book", "http://www.testlink.com/", "This is a test book");

INSERT INTO ItemBook (ItemName, Publisher) Values("Test Book", "Test Publisher");

SELECT * FROM Item i JOIN ItemBook b on b.ItemName = i.Name

Check the result in this print

Insert values into tables with a foreign key

Your Firebird code is not equivalent to your SQL Server code. Your SQL Server code starts at 1, while your Firebird code starts at 0. The first value generated for an identity column is 1, not 0, so your insert fails the foreign key constraint because there is no record in DOKUMENTY with ID_DOKUMENTU = 0. So, the immediate solution is to use declare variable cnt2 integer = 1; instead of declare variable cnt2 integer = 0;

However, your code is currently relying on the identity column actually starting at a specific value (1), and always incrementing by 1. That is not a safe solution, for example, if you try to run it now with this change, it will fail because the insert of the record with ID_DOKUMENTU = 1 was undone when your execute block failed, and the next record inserted will have a higher id.

Instead, modify your code to use the actual generated id:

execute block 
as
declare variable cnt2 integer = 1;
declare variable ID_DOKUMENTU type of column DOKUMENTY.ID_DOKUMENTU;
begin
    while (cnt2 < 1200) do
    begin
        insert into DOKUMENTY(DATA_KSIEGOWANIA) values(current_date) 
           returning ID_DOKUMENTU into ID_DOKUMENTU;

        insert INTO POZYCJE(ID_DOKUMENTU, KWOTA)
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE UNION ALL
        select (:ID_DOKUMENTU), MOD(RAND(),1000) FROM RDB$DATABASE ;
        cnt2 = cnt2 + 1;
    end
end

As an aside, MOD(RAND(),1000) does not do the same thing as RAND()*(10000) in your SQL Server code.

Insert data into separate tables related by foreign keys

<?php

if(isset($_POST['status'])) {
  $status = $_POST['status'];
}

if(isset($_POST['submit'])) {
    $title_bg = $_POST['title_bg'];
    $title_en = $_POST['title_en']; 
    $body_bg = $_POST['body_bg'];
    $body_en = $_POST['body_en'];

$connection =  new mysqli("localhost", "USER_XY", "PASSWD","DB"); 

if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  die ("<h1>can't use Database !</h1>");
  exit();
}
/* change character set to utf8 */
if (!$connection->set_charset("utf8")) {
printf("Error while loading 'character set utf8' : %s\n", $connection->error);
die();
} 

/**
 * First save the Post
 **/
    $query = "INSERT INTO posts(title_bg, title_en, body_bg, body_en, status, created) ";
    $query .= "VALUES('$title_bg', '$title_en', '$body_bg', '$body_en', '$status', now())";

      $result=$connection->query($query);
      // verify results
      if(!$result) {
        $message  = "ERROR SAVING POST : ".$connection->error . "\n";
        $connection->close();
        echo ($message);
        return false;
      }   

/**
 * get the last inster id of the Post
 **/        
    $post_id = $connection->insert_id;
    echo "Post id=".$post_id ."<br>\n";
    if(isset($_FILES['image'])) {
        foreach($_FILES['image']['name'] as $key => $name) {
            $image_tmp = $_FILES['image']['tmp_name'][$key];

            move_uploaded_file($image_tmp, './uploads/' . $name);
/**
 * now insert the image with the post_id
 **/                
    $query = "INSERT INTO `postimage` (`id`, `post_id`, `name`) ";
    $query .= "VALUES (NULL, '".$post_id."', '".$name."');";

                      $result=$connection->query($query);
      // verify results
      if(!$result) {
        $message  = "ERROR INSERT IMAGE : ".$connection->error . "\n";
        $connection->close();
        echo ($message);
        return false;
      } 
        }    
    }
    header("Location: upload_posts.php");  
}
?>
<form action="upload_posts.php" method="post" enctype="multipart/form-data">
    <div class="form-item">
        <label for="title_bg">Post title BG</label>
        <input type="text" name="title_bg">
    </div>

    <div class="form-item">
        <label for="title_en">Post title EN</label>
        <input type="text" name="title_en">
    </div>    
    <div class="form-item">
        <label for="body_bg">Post body BG</label>
        <textarea id="editor" name="body_bg" rows="10" cols="30"></textarea>
    </div>    

    <div class="form-item">
        <label for="body_en">Post body EN</label>
        <textarea id="editor2" name="body_en" rows="10" cols="30"></textarea>
    </div>    

    <div class="form-item">
        <label for="image">Image</label>
        <input type="file" name="image[]" multiple>
    </div>

    <div class="form-item">
        <label for="status">Post status</label>
        <select name="status">
            <option value="published">published</option>
            <option value="draft">draft</option>
        </select>
    </div>

    <div class="form-item">
        <input type="submit" class="form-submit" name="submit" value="Submit">
    </div>    
</form>

How do I insert data in two tables which are linked by foreign key?

Procedure :

DECLARE @uid INT
INSERT INTO dbo.userdetail
(Col1, Col2)
 VALUES (col1 value, col2 value)
 SELECT @uid =
 SCOPE_IDENTITY();
 INSERT INTO dbo.phonedetails
 (Col1,uid,col3) Values(col1 value,      @uid,col3 value)

---

Related Topics