Error: FOREIGN KEY relationship could not be added! #1452 - Cannot add or update a child row: a fore-CodePudding

I've encountered this problem while trying to create relationship between these two tables: vozila and accounts

Full error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`shscodin_cars`.`#sql-123257_12d31ca`, CONSTRAINT `#sql-123257_12d31ca_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vozila` (`vlasnik_id`))

I know that there're similar errors where people already asked for this problem, but by looking at their tables and answers I couldn't figure out how does it fits into mine situation here, that's why I am opening question.

-What am I trying to achieve?

Because I want to create a web application, with which I will display information about the vehicle (from the cars table), but of course I want to show only vehicles that are assigned to a particular user, not all.

When this error occurs?

Error that I am getting occurs when I try to create relationship between tables (vozila and accounts).

Here's code used to create these two tables:

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And for vozila

CREATE TABLE `vozila` (
  `id` int(11) NOT NULL,
  `make` varchar(50) COLLATE utf8_bin NOT NULL,
  `model` varchar(50) COLLATE utf8_bin NOT NULL,
  `godina` int(50) NOT NULL,
  `boja` varchar(50) COLLATE utf8_bin NOT NULL,
  `vin` varchar(50) COLLATE utf8_bin NOT NULL,
  `engine` varchar(50) COLLATE utf8_bin NOT NULL,
  `tip` varchar(50) COLLATE utf8_bin NOT NULL,
  `vlasnik` varchar(50) COLLATE utf8_bin NOT NULL,
  `regoz` varchar(50) COLLATE utf8_bin NOT NULL,
  `istek` date NOT NULL,
  `odometer` varchar(50) COLLATE utf8_bin NOT NULL,
  `napomena` varchar(50) COLLATE utf8_bin NOT NULL,
  `vlasnik_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for table `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `vozila`
--
ALTER TABLE `vozila`
  ADD PRIMARY KEY (`id`),
  ADD KEY `vlasnik_id` (`vlasnik_id`);

As you can see vlasnik_id is used as INDEX and I am trying to create relationship between vlasnik_id (as a reference) and id as FOREIGN KEY.

Can someone tell did I made good decision related to tables creation and have I added right keys for purposes of web app or something else?

CodePudding user response：

If you made this foreign key:

ALTER TABLE accounts ADD FOREIGN KEY (id) REFERENCES vozila (vlasnik_id);

Creating that constraint does succeed, given your setup.

But there are two problems.

First problem: each account can be the owner of only one car. Each account is stored on only one row, and its id has a single value. If that id must reference a row in vozila, then it can only reference one car. I think it would be more likely that one account should be allowed to be the owner of more than one car.

Second problem: The row with the referenced value must be inserted first, before you can insert a row that references it. In other words, you would have to insert to vozila first, with the value of the owner in vlasnik_id, before you can insert the row to accounts with that id. That seems backwards.

Both problems are solved if you define the foreign key this way:

ALTER TABLE vozila ADD FOREIGN KEY (vlasnik_id) REFERENCES account(id);

You create an account first, then insert one or more cars that reference that owner's id. The account can own more than one car.