This looks like a more or less complete solution for creating the tables and doing inserts.
Primary table: CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY, 'name' TEXT NOT NULL, 'filePath' TEXT NOT NULL, 'iH1' INTEGER NOT NULL, 'iH2' INTEGER NOT NULL, 'iH3' INTEGER NOT NULL, 'iH4' INTEGER NOT NULL, 'formula' TEXT NOT NULL, 'fw' FLOAT NOT NULL, UNIQUE(iH1, iH2, iH3, iH4) ON CONFLICT FAIL ) Satellite table: CREATE TABLE Project1( 'Structure_id' INTEGER NOT NULL, 'class' STRING, 'status' STRING, 'RI17-1' FLOAT, FOREIGN KEY(Strucutre_id) REFERENCES Structure(id) ) There are three cases for doing inserts of the data for phosphoserine, which is distributed over both tables. record phosphoserine, data for Structure table: name = phosphoserine filePath = phosphoserine.mol iH1 = 185073 iH2 = 856147 iH3 = 73543 iH4 = 25338 formula= C3H8NO6P fw = 185.073 record phosphoserine, data for Project1 table: class = C0248 status = M RI17-1 = 15.0 these statements include some ruby pseudocode 1. Insert data to Structure table only, do not insert Project1 data @db.execute "INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine', 'phosphoserine.mol', 185073, 856147, 73543, 25338, 'C3H8NO6P', 185.073)" 2. Sequentially insert data to Structure and then Project1 @db.execute "INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine', 'phosphoserine.mol', 185073, 856147, 73543, 25338, 'C3H8NO6P', 185.073)" # capture the row number where phosphoserine was inserted to Structure @Structure_id = @db.last_insert_row_id # use the value of Structure_id to link phosphoserine data in Project1 to phosphoserine data in Structure # the implicit rowid will be the primary key for Project1 and so is not handled explicitly @db.execute "INSERT INTO Project1(Structure_id, class, status, RI17-1) VALUES(Structure_id, C0248, M, 15.0)" 3. Insert phosphoserine data to Project1 at some later time, meaning in a situation where there is already a record in Structure for phosphoserine # lookup the row number in Structure where the phosphoserine record is stored using the 4 int key values @Structure_id = @db.execute "SELECT id FROM Structure WHERE iH1 = 185073 AND iH2 = 856147 AND iH3 = 73543 AND iH4 = 25338;" # use the value of Structure_id to link phosphoserine data in Project1 to phosphoserine data in Structure # the implicit rowid will be the primary key for Project1 and so is not handled explicitly @db.execute "INSERT INTO Project1(Structure_id, class, status, RI17-1) VALUES(Structure_id, C0248, M, 15.0)" Excepting that the ruby is probably not quite right here and that the SQL instructions are using literals and not parameters, does this look like a reasonable setup? The Structure table will have more records than the satellite tables and data will be added to the satellite tables more frequently. I think this allows for addition of new tables of data related to previously stored structures (Project2, Project3, etc) as needed, and also allows for a quick method of looking up structures that might have been registered under a different name. Is ON CONFLICT FAIL what I want for the Structure table, or would ABORT/ROLLBACK make more sense? *LMHmedchem* -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65567.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
