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

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)

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