CODEWITHSUNDEEP
phpsqlforeign-keys
major697
major697

Reputation: 1862

Foreign key and update records

I created three table: products, article and category.I joined tables for foreign key:

CREATE TABLE `products` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `added` varchar(30) NOT NULL,
  `nazwa` int(11) NOT NULL,
  `kategoria` int(11) DEFAULT NULL,
  `sn` varchar(100) NOT NULL,
  `quantity` int(10) NOT NULL,
  `data` date NOT NULL,
  `godzina` time NOT NULL,
  `warranty` date NOT NULL,
  FOREIGN KEY (`nazwa`) REFERENCES `article`(`article_id`),
  FOREIGN KEY (`kategoria`) REFERENCES `category` (`category_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

Everything works correctly. The problem is when I try update record in table products.

I try this: 

    UPDATE `products` 
SET 
    `added` = 'michal',
    `nazwa` = 'ball',
    `kategoria` = 'toys',
    `sn` = '14dsa564dsa65',
    `data` = NOW(),
    `godzina` = CURTIME()

WHERE
    `id` = 13

but this query not working.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (test.products, CONSTRAINT products_ibfk_1 FOREIGN KEY (nazwa) REFERENCES article (article_id)) 0.157 sec

Look when i wont return records from the datbase I used this query:

SELECT 
    products.id,
    products.added,
    article.nazwa,
    category.kategoria,
    products.sn,
    products.quantity,
    products.data,
    products.godzina,
    products.warranty
FROM
    products
        LEFT JOIN
    category ON products.kategoria = category.category_id
        LEFT JOIN
    article ON products.nazwa = article.article_id
ORDER BY id

For update I used:

UPDATE `products` 
SET 
    products.added= 'michal',
    article.nazwa= 'ball',
    category.kategoria = 'toys',
    products.sn= '14dsa564dsa65',
    products.data = NOW(),
    products.godzina = CURTIME(),

LEFT JOIN
    category ON products.kategoria = category.category_id
        LEFT JOIN
    article ON products.nazwa = article.article_id
WHERE
    `id` = 13

but throws error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN category ON products.kategoria = category.category_id LEFT' at line 10 0.000 sec

Upvotes: 0

Views: 761

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94662

Your issue is that the article table does not contain a row with a 

article_id ='piłeczka pingpongowa'

and as this column is defined as an INT(11) it would not go into this column anyway.

And your category tables does not contain a row with 

category.category_id = 'zabawki2'

This column is also defined as INT(11) in your schema! So this also will not work

You cannot create children before you create the parent!

Also remove both the JOIN's they are not needed

Upvotes: 1

Related Questions