m:n Beziehung und Insert

[Svensen]

Hallo.
Hab da mal ne Frage und vielleicht kann mir hier jemand helfen.
Also, habe drei Tabellen.

CREATE TABLE TableA (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Nummer varchar(32) NOT NULL);

CREATE TABLE TableB(ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Namen varchar(32) NOT NULL);

CREATE TABLE TableA_TableB(TableA_ID integer NOT NULL, TableB_ID integer NOT NULL, CONSTRAINT TableA_TableB_PK PRIMARY KEY (TableA_ID,TableB_ID),
FOREIGN KEY (TableA_ID) REFERENCES TableA(ID);
FOREIGN KEY (TableB_ID) REFERENCES TableB(ID);

Wie man vielleicht sieht geht es um eine m:n Beziehung.
Mein Frage ist, wie bekomm ich da nen saueberen Insert hin.
Ich mache das bis jetzt so:

INSERT INTO TableB (Name) VALUES ("Blablub")
INSERT INTO TableA (Nummer) VALUES (12345)

INSERT INTO TableA_TableB (TableA_ID, TableB_ID) VALUES
((SELECT TableB.ID FROM TableB,TableA WHERE TableA.Nummer = 12345 AND TableB.Name = "Blablub")
(SELECT TableA.ID FROM TableB,TableA WHERE TableA.Nummer = 12345 AND TableB.Name = "Blablub"))

Geht das vielleicht irgendwie sauberer und verschachtelt in einem Insert Statement über nen Trigger oder so ähnlich?

Vielen Dank schonmal im vorraus

LG

 

[Svensen]

Also habe jetzt noch etwas getestet und das Problem ist, in Tabelle A können die Nummern doppelt vorkommen aber sollen zu unterschiedlichen Name in Tabelle B verknüpft werden.
Nun wird beim Insert in TableA_TableB die erste Nummer genommen die er findet.
Ich bräuchte als eine Möglichkeit die letzte ID von Tabelle A rauszufinden und diese zu Tabelle B zu verknüpfen.

 

[akretschmer]

der saubere Weg wäre, die Funktionen der Datenbank zu nutzen, um die letzte vergebene ID zu ermitteln. Das sollte auch SQLite können, und das sollte in dessen Referenmanual beschrieben sein.

Eine sehr elegante Lösung bietet PostgreSQL via wCTE (writeable Common Table Expressions). Wir bauen uns mal 3 Tabellen ähnlich Deiner:

test=*# create table tab_a(id serial primary key, val text);
CREATE TABLE
test=*# create table tab_b(id serial primary key, val text);
CREATE TABLE
test=*# create table tab_ab(id_a int references tab_a, id_b int references tab_b, val text);
CREATE TABLE

Das sind nun 3 leere Tabellen, jeweils mit einen Autoincrement-Feld (hier SERIAL) und einen Text-Feld. Mit nur 1 (in Worten: EINEM) SQL füge ich in alle 3 gleichzeitig und atomar Werte ein:

test=*# with my_a as (insert into tab_a(val) values ('test tab a') returning id), my_b as (insert into tab_b (val) values ('test tab b') returning id) insert into tab_ab select my_a.*, my_b.*, 'test tab_ab' from (select * from my_a) my_a cross join (select * from my_b) my_b;
INSERT 0 1
test=*# select * from tab_a;
 id |    val     
----+------------
  1 | test tab a
(1 row)

test=*# select * from tab_b;
 id |    val     
----+------------
  1 | test tab b
(1 row)

test=*# select * from tab_ab;
 id_a | id_b |     val     
------+------+-------------
    1 |    1 | test tab_ab
(1 row)

test=*#

Aber das wird SQLite nicht können. Also, schaue wie man die letzte vergebene ID ermittelt und nutze das.

 

[Svensen]

Ok. Vielen Dank.
Habe die Lösung gefunden und es war "eigentlich" recht einfach.
Du hast recht, es geht um die letzte ID.
Der überarbeitete Insert sieht nun wie folgt aus:

INSERT INTO TableAB (TableA_ID, TableB_ID) VALUES
(
(SELECT TableB.ID FROM TableB WHERE TableB.Name = "Blablub"),
(SELECT MAX (TableA.ID) FROM TableA)
)

Man könnte natürlich bei TableB auch direkt nach MAX ID suchen

INSERT INTO TableAB (TableA_ID, TableB_ID) VALUES
(
(SELECT MAX (TableB.ID) FROM TableB),
(SELECT MAX (TableA.ID) FROM TableA)
)

 

[akretschmer]

nein, max() ist nicht gut. Wenn Du jetzt ein Insert machst, dann in einer anderen Client-Connection ein weiterer Insert kommt, und Du nun max() aufrufst, bekommst die ID der anderen Session.

How to retrieve the last autoincremented ID from a SQLite table?
SQLite: How to get the “autoincrement” value from the last insert | alvinalexander.com

 

[Svensen]

Ahso, ja des kann nicht passieren.
Geht um eine lokale SQLite DB auf einem WIndows CE Gerät ohne Anbindung ans Netz.

 

[akretschmer]

Ahso, ja des kann nicht passieren.
Geht um eine lokale SQLite DB auf einem WIndows CE Gerät ohne Anbindung ans Netz.

Kein Grund es nicht richtig zu machen.

 

[Svensen]

Kein Grund es nicht richtig zu machen.

Stimmt auch wieder