> It looks like my insert syntax should be, > INSERT INTO Structure > VALUES(null,'phosphoserine','phosphoserine.mol',185073,856147,73543,25338,'C3 > H8NO6P',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,'C3 > H8NO6P',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?
INSERT INTO tablename (a, b, c, ... z) VALUES ( ... ) The field list may be omitted in which case it is assumed to contain, in order, all the explicitly defined fields from the table declaration. If this is the field list you want to use, you may either explicitly list the fields, or omit the list entirely. If you want to insert values into a subset of the fields in the table, or use a different binding order than the order the fields were declared in the schema, then you need to specify the field list. Any field not listed in the field list will be given the default value specified in the table declaration, or null if no default was specified (in other words the default default is default null). If null is not permitted, the statement will fail. It is good practice to specify the field names for two reasons: (1) as Igor said, it is self documentation; and, (2) because you can then change the table declaration and add new fields, or re-order them, without changing the SQL -- this becomes more important as feeping creaturitis overtakes your database and you find you are adding new fields or references that do not impact already working code. > 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 > ) The column "id" (INTEGER PRIMARY KEY) is the row number of the row in the table. If you do not declare an INTEGER PRIMARY KEY explicitly, then the row number is available in an implicit field called "rowid" (since every row must obviously have a unique row number). An implicit rowid can be retrieved by name in a SELECT or listed as a field in an insert to insert a specific row. You cannot use an implicit rowid as a parent key in a foreign key constraint. The implicit "rowid" is not part of the returned data in a SELECT *, nor is it in the default field list in an INSERT INTO. Explicitly named INTEGER PRIMARY KEY are included in SELECT * and in the field list of INSERT INTO. In all other respects the explicit "id" INTEGER PRIMARY KEY is the same as the implicit "rowid" INTEGER PRIMARY KEY. In other words, the INTEGER PRIMARY KEY column always exists and you either give it an explicit name or it is implicitly declared and called "rowid". If it is explicitly declared then you are expected to always deal with that column somehow in INSERT and SELECT operations because it is an explicit field. If the row number is explicitly made a column, then since you are expected to have to deal with it, you can reference it as a parent key in foreign key definitions. The INTEGER PRIMARY KEY (whether implicit or explicitly named) is always magically incrementing. You cannot store a row without a row number. Therefore, not specifying a row number on an insert operation (by inserting with a null explicit INTEGER PRIMARY KEY or not explicitly specifying a value for the implicit rowid) means "store the row and give it a new row number". For both implicit and explicit INTEGER PRIMARY KEY, the new "row number" will be one greater than the largest row number currently in the table (and 1 if there are no rows currently in the table). If this overflows then it will be some other unused row number (you cannot store two different rows at the same location). If there is no unused row number, then the operation fails. AUTOINCREMENT modifies this by adding the word "ever" to the above process and causes the database to store the "largest ever used" row number for the table, and to only generate row numbers one larger than the largest row number ever in the table. If that overflows, the operation fails. This slightly increases overhead for every insert because now instead of max(rowid)+1 being the new rowid, the max(rowid ever)+1 is the new row number, and that "rowid ever" must be stored persistently somewhere and must be maintained. Sometimes you need a guarantee that rowid's will never be re-used (AUTOINCREMENT), and sometimes you don't. INTEGER PRIMARY KEY columns autoincrement without the autoincrement keyword. The AUTOINCREMENT keyword means that there is meaning assigned to the value of the rowid column above and beyond mere existential uniqueness -- it adds everness. So, if you do not need to reference the rowid or it is not meaningful beyond being being a unique row identifier, then let it be an implicit declaration. Only declare the INTEGER PRIMARY KEY for tables where you actually need to reference that value (for example as a parent key in a foreign key constraint). Then only use the AUTOINCREMENT keyword if your requirements go beyond mere existential uniqueness. If you wish you can always declare a column as "id integer primary key" in every table and then you will always have to deal with it everywhere. Some people do that because they come from a non-database background -- such as "thinking in spreadsheets" or have caught a nasty case of the object-oriented disease ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
