Insert data into a table with a foreign key SQL

I need to insert some data into the ‘ItemBook’ table after inserting the following values for one row for the ‘Item’ Table:

Name='Clippers', itemLink='amazon.com' description='hair clippers'

Now I would also like to insert some data to the ItemBook table as well but I am not sure on how to do this with a table that has a foreign key. Here is the SQL code:

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)
);

My attempt:

INSERT INTO itemBook (Name, Publisher)
VALUES ('Clippers', 'Bob');

Error Msg:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Other Attempt:

INSERT INTO eventbook (EventName, Publisher)
SELECT E.name
FROM event E
WHERE E.name = eventbook.EventName;

Error Message:

Error Code: 1054. Unknown column 'eventbook.EventName' in 'where clause'

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x