transfer records with foreign key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

transfer records with foreign key

Roman Fleysher
Dear SQLiters,

I have two tables linked by a foreign key, linkID. I need to transfer content of these two tables into two corresponding tables in another database preserving the link. However, the second database already has records and numeric value of linkID can not be preserved. Nor its value is important because it simply links the tables. How do I INSERT into two tables, preserving the link while allowing for value of linkID to change. LinkID is defined as INTEGER PRIMARY KEY.

It looks to me that I should create a temporary view on the both tables in the second database and try to use INSTEAD OF trigger. Is that the right way? Or I have to create a temp table that maps old linkID to new linkID, somehow.

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: transfer records with foreign key

R Smith

On 2017/08/11 7:50 PM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I have two tables linked by a foreign key, linkID. I need to transfer content of these two tables into two corresponding tables in another database preserving the link. However, the second database already has records and numeric value of linkID can not be preserved. Nor its value is important because it simply links the tables. How do I INSERT into two tables, preserving the link while allowing for value of linkID to change. LinkID is defined as INTEGER PRIMARY KEY.
>
> It looks to me that I should create a temporary view on the both tables in the second database and try to use INSTEAD OF trigger. Is that the right way? Or I have to create a temp table that maps old linkID to new linkID, somehow.

It's a little hard to follow, but if I understand correct, you have one
table with a foreign key parent in another table. You wish to amalgamate
these tables with two similar tables in another database while retaining
the foreign-Key relationships, BUT, the target tables (to be added to)
already has the relationship set up and might have keys (or key-values
rather) that may already have been used and so would be duplicated if
you simply INSERT all the data - which is obviously not possible or wanted.

Further to this, you do not care about the actual values in these keys,
only the relationships.

The question I am specifically missing is: Do you mean for this to
happen on a regular basis? Or continuously? Or only once after which you
will simply continue life using the second DB's tables?

Another question: How large are these tables? i.e. Will it break your
storage medium if we duplicate them into Temp tables first?

Either way, assuming that it is a once-off operation (or at least
something only done once in a while), the solution can be had by simply
appending after the known last Key in the target table, somewhat like this:


   -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
version 2.0.2.4.
   --
================================================================================================

   -- The first bit is just some test setup to try and mimic the 4
tables in question.
   -- AP and AC is the Parent and Child tables in DB A, and
   -- BP and BC the same Parent and Child Tables in DB B.
   -- The Relation Key is on "linkID"
CREATE TABLE AP (
   ID INTEGER PRIMARY KEY,
   PVal TEXT
);
CREATE TABLE AC (
   ID INTEGER PRIMARY KEY,
   linkID INT REFERENCES AP(ID),
   CVal TEXT
);
CREATE TABLE BP (
   ID INTEGER PRIMARY KEY,
   PVal TEXT
);
CREATE TABLE BC (
   ID INTEGER PRIMARY KEY,
   linkID INT REFERENCES BP(ID),
   CVal TEXT
);

   -- Some test values to make the relationships easy to follow.
INSERT INTO AP(ID,PVal) VALUES (1, 'Fleysher'), (2,'Smith');
INSERT INTO AC(ID,linkID,CVal) VALUES (1, 1, 'Roman'), (2, 1, 'Sam'),
(3, 2, 'Ryan'), (4,2, 'John');
INSERT INTO BP(ID,PVal) VALUES (1, 'Hipp'), (2,'Kennedy');
INSERT INTO BC(ID,linkID,CVal) VALUES (1, 1, 'Richard'), (2, 2, 'Dan'),
(3, 2, 'John F.');

   -- Some queries to show how the relations manifest in the 2 separate DBs:
SELECT AC.ID, AC.CVal, AP.PVal
   FROM AC
   JOIN AP ON AP.ID = AC.linkID
;
   --      ID      | CVal  | PVal
   -- ------------ | ----- | --------
   --       1      | Roman | Fleysher
   --       2      | Sam   | Fleysher
   --       3      | Ryan  | Smith
   --       4      | John  | Smith

SELECT BC.ID, BC.CVal, BP.PVal
   FROM BC
   JOIN BP ON BP.ID = BC.linkID
;
   --      ID      | CVal    | PVal
   -- ------------ | ------- | -------
   --       1      | Richard | Hipp
   --       2      | Dan     | Kennedy
   --       3      | John F. | Kennedy


-- Here starts the actual script to solve the combination:

   -- I couldn't preserve the value across CTEs, so opted for a TEMP
table in stead to
   -- get the base value from which to add the A DB values.
CREATE TEMP TABLE TX AS SELECT MAX(ID) AS vMin FROM BP;

INSERT INTO BP(ID,PVal)
SELECT (AP.ID+TX.vMin), AP.PVal
   FROM AP,TX
;
INSERT INTO BC(linkID,CVal)
SELECT (AC.linkID+TX.vMin), AC.CVal
   FROM AC,TX
;
DROP TABLE TX;

-- That's the end of the combination script.

   -- A query to simply show the combined DB to ensure the principle is
sound.
SELECT BC.ID, BC.CVal, BP.PVal
   FROM BC
   JOIN BP ON BP.ID = BC.linkID
;
   --      ID      | CVal    | PVal
   -- ------------ | ------- | --------
   --       1      | Richard | Hipp
   --       2      | Dan     | Kennedy
   --       3      | John F. | Kennedy
   --       4      | Roman   | Fleysher
   --       5      | Sam     | Fleysher
   --       6      | Ryan    | Smith
   --       7      | John    | Smith


-- The rest is just cleanup for this test script.
DROP TABLE AC;
DROP TABLE AP;
DROP TABLE BC;
DROP TABLE BP;

-- And proof that no FK relations were harmed in the making of this script:

   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.040s
   --                 Total Script Query Time:         0d 00h 00m and
00.012s
   --                 Total Database Rows Changed:     17
   --                 Total Virtual-Machine Steps:     1283
   --                 Last executed Item Index:        19
   --                 Last Script Error:
   --
------------------------------------------------------------------------------------------------

   -- 2017-08-11 22:16:17.814  |  [Success]    Script Success.
   -- 2017-08-11 22:16:17.816  |  [Success]    Transaction Rolled back.
   -- -------  DB-Engine Logs (Contains logged information from all DB
connections during run)  ------


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...