Geek Answers Handbook

How to use an auto-incrementing primary key as a foreign key?

This is what I am trying to do:

I have 2 tables ...

CREATE TABLE `parent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `parent_id` int(11) DEFAULT NULL, `related_ids` int(11) DEFAULT NULL, KEY `parent_id` (`parent_id`), KEY `related_ids` (`related_ids`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And then the restriction:

 ALTER TABLE `parent` ADD FOREIGN KEY (`id`) REFERENCES `child` (`parent_id`);

As you can see, the parent of the table has an automatically increasing primary key "id", which is also used as a foreign key for the child table.

Now I want to insert a record into the parent table, for example:

 INSERT INTO parent SET DATA="abc";

And he is not mistaken:

Cannot add or update a child row: a foreign key constraint is not met ( anacorbero . Parent, CONSTRAINT parent_ibfk_1 FOREIGN KEY ( id ) LINKS child ( parent_id ))

I understand that it fails because it does not find the recorded record in the child table. If I start by creating an entry in the child table, set its parent_id to 1, and then reset the parent table auto-increment counter (so the next insert will have id = 1), it works! But this is not a solution.

I do not see the usefulness of insert locks if there is no related row in the child table ...

I'm just trying to make a one-to-many relationship ...

(I know I can use JOIN, but I'm trying to use table relations, for data integrity, as well as metadata for PHP)

+4
source share
2 answers

It looks like you are referencing and referencing tables in reverse order. You can do:

 ALTER TABLE `child ` ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`);

You can also define a foreign key in a CREATE TABLE statement as follows:

 CREATE TABLE `parent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `parent_id` int(11) DEFAULT NULL, `related_ids` int(11) DEFAULT NULL, KEY `parent_id` (`parent_id`), KEY `related_ids` (`related_ids`), FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Test case:

 INSERT INTO parent (`data`) VALUES ('test data 1'); Query OK, 1 row affected (0.01 sec) INSERT INTO parent (`data`) VALUES ('test data 2'); Query OK, 1 row affected (0.01 sec) INSERT INTO child (`parent_id`, `related_ids`) VALUES (1, 100); Query OK, 1 row affected (0.01 sec) INSERT INTO child (`parent_id`, `related_ids`) VALUES (2, 100); Query OK, 1 row affected (0.01 sec) INSERT INTO child (`parent_id`, `related_ids`) VALUES (3, 100); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
+8
source

Uh ... I think I got it back. It seems that I need to add a foreign key to the child table, for example:

 ALTER TABLE `child` ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`);

It's hard for me to work with MySQL terminology. Can you blame me?

+1
source

More articles:


All Articles