Foriegn Keys - Are 2 Queries Needed?

Or can it be done with 1?

[Nick_W]

Hi all,

I've been using 2 seperate inserts and (in php) mysql_insert_id() in the second query to add a foreign key to a table. Like this:

table1
id - (auto inc primary key)
stuff
more stuff

table2
id (unique - must have same val as id in table1)
stuff
stuff

Is it possible to insert some values into both tables at once and keep the foreign key thing happening or am I stuck with 2 seperate inserts?

Many thanks!

Nick

[brotherhood of LAN]

hey Nick, still making that blog software eh ;)

Any chance you can post what the fields actually do. AFAIK you shouldnt have to be inserting 2 unique keys for the same process. If the info is normalised one piece of info should be found in one place? Just have a hunch the tables can be re-ordered...

You can insert more than one set of values into the one table but I think you'll need two statements for the two tables.

Can't really slow things down all that much since mySQL doesnt really have to look up anything to "add something to the end of the db".

not much fact in there but searching for "mysql double insert" doesnt yield much info ;)

[Nick_W]

OK - You asked for it!

CREATE TABLE posts (
id INT NOT NULL AUTO_INCREMENT,
author INT NOT NULL,
date TIMESTAMP NOT NULL,
title VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
text TEXT NOT NULL,
INDEX(author),
INDEX(date),
PRIMARY KEY(id)
);

CREATE TABLE controls (
id INT NOT NULL AUTO_INCREMENT,
postid INT NOT NULL,
lastpost TIMESTAMP,
section INT NOT NULL,
level INT NOT NULL,
live ENUM('3','2','1', '0') NOT NULL,
UNIQUE(postid),
INDEX(lastpost),
INDEX(section),
INDEX(level),
PRIMARY KEY(id)
);

I just mean when I insert a new post. Can I make postid in the 2nd table = id in the first table with one insert?

Cheers!

Nick

[brotherhood of LAN]

I just mean when I insert a new post. Can I make postid in the 2nd table = id in the first table with one insert?

see that's where im no sure, but you can do that with the one table no problem. and i dont think inserting into two tables with 2 statements is all that detrimental either.

check out sticky for side info about the blog.

I'm just thinking an UPDATE/INSERT statement is what would be needed, i.e. you update the authors ability to edit the inserted blog? ;)

[Nick_W]

Hmm... Your last paragraph has me lost but, it's no biggie to use 2 INSERTS to add an post to the blog.

I was just sitting here working on the php function and wondered if there might be somthing I'm missing...

Thanks!

Nick

[brotherhood of LAN]

>lost me

I'm assuming you are updating the tables when an author posts a new blog ;)

I'd be updating the authors table saying he has certain rights to edit a certain blogid and inserting the blog - i.e. UPDATE the authors table INSERT new blog.

>live ENUM('3','2','1', '0') NOT NULL,

you could have a 16 bit string for example, the first 14 might be the id of the blog and the last 2 are the editing rights. have a blob field for the author that contains the pages he has the right to edit?

Just thinking your tables could be re-arranged for precision/recall. I guess it doesnt really address your first question but food for thought ;) It might not need be a problem in the first place.

//added
if you have a blog and author table on their own, then that's how I would do it. Anything else must be bells and whistles ;)