Unique links von einer Tabelle in eine Andere erzeugen

[DonLuigi]

Hallo zusammen,

ich würde gerne Daten in einer Tabelle mit den Daten in einer Anderen verknüpfen. Dabei würde ich gerne die Daten immer nur einfach verknüpfen, sodass kein element 2-fach verlinkt wird. Bsp:

/* Tabelle 1 neu erzeugen */
DROP TABLE IF EXISTS Nodes;
CREATE TABLE Nodes (
    ID   INTEGER PRIMARY KEY AUTOINCREMENT
                 UNIQUE
                 NOT NULL,
    Data INTEGER NOT NULL
);
/* Tabelle 1 befüllen */
INSERT INTO Nodes (Data) VALUES (2), (3), (9), (20), (19), (13), (29), (25), (9), (25), (20), (24);


/* Tabelle 2 neu erzeugen */
DROP TABLE IF EXISTS Links;
CREATE TABLE Links (
    ID     INTEGER PRIMARY KEY AUTOINCREMENT
                   UNIQUE
                   NOT NULL,
    Data   INTEGER NOT NULL,
    NodeID INTEGER
);
/* Tabelle 2 befüllen */
INSERT INTO Links (Data) VALUES (9), (9), (13), (19), (20), (20), (21), (24), (25), (25), (29), (30), (32);

/* ... und jetzt die links erzeugen */
UPDATE Links
   SET NodeID = (
       SELECT Nodes.ID
         FROM Nodes
        WHERE Nodes.Data = Links.Data
          /* prüfe, dass der Link unique ist: */
          AND Nodes.ID NOT IN (SELECT NodeID FROM Links WHERE NodeID IS NOT NULL) );

Wie mir scheint, wird das letzte SELECT in meinem UPDATE query nur einmal für das UPDATE ausgeführt, nicht aber für jede Zeile des Updates. Damit sind die Daten natürlich nicht aktuell und ein Node kann mehrfach in den Links verlinkt werden.

Leider scheitere ich daran, die NodeID in der Link Tabelle "UNIQUE" zu bekommen. Ich kann sie aber auch nicht als UNIQUE spezifizieren, da in einem anderen/späteren Schritt auch mehrfache Links erzeugt werden sollen.

Ist mein Problem verständlich?
Hat jemand eine Idee wie man das lösen kann?

Ich bedanke mich schonmal für eure Ideen und Hilfen.
Grüße,
Basti

 

[akretschmer]

wenn ich Dich richtig verstehe, hier eine TRIGGER-Lösung:

test=*# select * from nodes;
 id | data
----+------
  1 |    2
  2 |    3
  3 |    9
  4 |   20
  5 |   19
  6 |   13
  7 |   29
  8 |   25
  9 |    9
 10 |   25
 11 |   20
 12 |   24
(12 rows)

test=*# \d links
                             Table "public.links"
 Column  |  Type   | Collation | Nullable |              Default             
---------+---------+-----------+----------+-----------------------------------
 id      | integer |           | not null | nextval('links_id_seq'::regclass)
 data    | integer |           |          |
 node_id | integer |           |          |
Indexes:
    "links_pkey" PRIMARY KEY, btree (id)
    "links_node_id_key" UNIQUE CONSTRAINT, btree (node_id)
Foreign-key constraints:
    "links_node_id_fkey" FOREIGN KEY (node_id) REFERENCES nodes(id)
Triggers:
    set_node_id BEFORE INSERT ON links FOR EACH ROW EXECUTE FUNCTION get_node_id()

test=*# create or replace function get_node_id() returns trigger as $$declare _id int; begin select id from nodes where data = new.data limit 1 into _id; new.node_id = _id; raise notice '%',new.node_id; return new; end; $$language plpgsql;
CREATE FUNCTION
test=*# insert into links (data) values (9);
NOTICE:  3
INSERT 0 1
test=*# insert into links (data) values (9);
NOTICE:  3
ERROR:  duplicate key value violates unique constraint "links_node_id_key"
DETAIL:  Key (node_id)=(3) already exists.
test=*#

allerdings mit PostgreSQL.

 

[Dravion]

Hört sich für mich nach einer Aufgabe für Foreign Keys an.

 

[dabadepdu]

Bezogen auf Dein Update Statement erstmal der Hinweis, UNIQUE ist etwas anderes als EINS(1).
Dein Statement liefert zu viele Werte, um zu funktionieren. Das select statement, das den Wert für NODE_ID liefert, darf nur einen Wert liefern, mehr passt nicht rein.
Du könntest das lösen, in dem Du von den gelieferten Werten immer nur einen verwendest. Den ersten, den kleinsten, ...

Ob dieser Wert dann unique ist, ist eine andere Frage.