1

Lets say I have two tables, posts(id_pk,title) and posts_content(post_id_pk,blob). Whenever I create a row in posts, I want a complimentary row to be created in posts_content, with the same id. If I just use auto_increment on both, when two posts are created simultaneously, they might get switched. What is the correct way to do this?

2

Let's say you have this:

CREATE TABLE posts
(
    id int not null auto_increment,
    title varchar(100),
    primary key (id)
) ENGINE=InnoDB;
CREATE TABLE posts_content
(
    post_id int not null,
    blobdata blob,
    primary key (post_id),
    CONSTRAINT `FK_posts` FOREIGN KEY (`post_id`) REFERENCES posts (`id`)
) ENGINE=InnoDB;

You could perhaps run the two INSERTs like this:

START TRANSACTION;
INSERT INTO posts (title) VALUES ('some-title');
SET @GivenPostID = LAST_INSERT_ID();
INSERT INTO posts_content (post_id,blobdata)
VALUES (@GivenPostID,'oxbdsixds');
COMMIT;

Give it a Try !!!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.