Insert Script - primary index

[Generator3Punkte]

Hello...

...I have a problem with inserting into more then one table.

Ive got 3 Tables:

T1:
id (primery key with autoincrease )
Data...int


T2:
id (primery key with autoincrease )
OtherData...int


T3:
id (primery key with autoincrease )
T1_id...is connceted by a key with the id of T1
T2_id...is connceted by a key with the id of T2
some other Data


Now I want to write a UpdateScript that inserts:
One Row into T1, One Row into T2 and finaly:
One Row with the new ids from T1 and T2 into T3

Thank you

 

[guelphdad]

You want to use last_insert_id across multiple tables, see

http://guelphdad.wefixtech.co.uk/sqlhelp/lastinsertid.shtml

for details.

 

[r937]

T3 should not have its own auto_increment id

create table T3
( T1_id integer not null 
, T2_id integer not null 
, foreign key ( T1_id ) references T1 ( id )
, foreign key ( T2_id ) references T2 ( id )
, [b]primary key ( T1_id
              , T2_id )[/b]
);

r937.com | rudy.ca

 

[Generator3Punkte]

@guelphdad:
ok i try it

@r937:
In my system every table got an own id so i can reference to it. What is wrong about?

 

[r937]

the "own id" is completely unnecessary for relationship tables like T3

wasted space, wasted cycles, poor performance unless T1_id and T2_id are also indexed, and, most importantly of all, unless there is a unique constraint on (T1_id,T2_id), you run the risk of entering a duplicate relationship, which, if you are somehow preventing this through application logic, is even more unnecessary processing

r937.com | rudy.ca

 

[Generator3Punkte]

@r937:

Ok...

...the Table T3 is just an simple Version of my tables and there is a lot of other data in it as you can see in my 1st post

T3:
id (primery key with autoincrease )
T1_id...is connceted by a key with the id of T1
T2_id...is connceted by a key with the id of T2
some other Data <==== !!!!!