How to use an auto incremented primary key as a foreign key as well?

Question

This is what I'm 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 a constraint:

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

As you can see the table parent has an auto-incremented primary key "id", which is also being used as a foreign key for the child table.

Now I want to insert a record in the parent table, like this:

INSERT INTO parent SET DATA="abc";

And it fails with error:

Cannot add or update a child row: a foreign key constraint fails (myschema.parent, CONSTRAINT parent_ibfk_1 FOREIGN KEY (id) REFERENCES child (parent_id))

I understand that it fails because it doesn't find a referred record in the child table. If I start by creating a record in the child table, set it's parent_id to 1, then reset the auto-increment counter of the parent table (so that the next insert will have id = 1), it works! But that's not a solution.

I don't see the utility of the insert blocking if there is no related row in the child table...

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

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

Answer 1

It looks like you have the referencing and referenced tables in reverse. You may want to do:

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

You can also define the foreign key in the 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