Hello, I have made some progress. This is what my Structure table looks like now,
CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY AUTOINCREMENT, '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 ) This is the main table and I am linking all other tables using the id value from Structure (Structure_id), I have a question about the syntax for inserts. If I have the following data to be inserted to Structure, Structure Data for phosphoserine: name = phosphoserine filePath = phosphoserine.mol iH1 = 185073 iH2 = 856147 iH3 = 73543 iH4 = 25338 formula= C3H8NO6P fw = 185.073 It looks like my insert syntax should be, INSERT INTO Structure VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073); where using null for id invokes AUTOINCREMENT. Is this the right syntax for sqlite? I have also seen versions of insert where the Table fields are defined and look like, INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3H8NO6P',185.073); Would I still use null here for the id, or do I not have this right? Is there some reason for preferring one version over the other if both are valid? Satellite table would look like the following where Structure_id is the common field linking data in Structure and Project1. CREATE TABLE Project1( id INTEGER PRIMARY KEY AUTOINCREMENT, 'Structure_id' INTEGER NOT NULL, 'class' STRING, 'status' STRING, 'RI17-1' FLOAT, FOREIGN KEY(Strucutre_id) REFERENCES Structure(id) ) I will post a bit about how to do the inserts to satellite tables when I have the inserts for the structure table setup up correctly. If Simon is reading this, I have not ignored your comment about having 500 columns in a table being an issue, I just am trying to get the basic syntax working first. None of those fields will ever have a null value. There are many zero's but those are not nulls and have meaning, like a family having 0 children. *LMHmedchem* -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65496.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
