On Sun, Mar 14, 2010 at 8:26 AM, Simon Slavin <slav...@bigfraud.org> wrote: > My real database is too ridiculous to explain here, so here is an analogy: > > Table 1: Authors -- columns id (INTEGER PRIMARY KEY), name (TEXT) > Table 2: Books -- columns id (INTEGER PRIMARY KEY), author > (INTEGER), title (TEXT) > > When I enter a new book, I want the author to default to the last author in > the database. (For the sake of this example you can ignore tricks involving > deleting authors and reusing ids.) > > Suppose I add an author, than many books, then another author and many books > by the second author. According to > > http://www.sqlite.org/syntaxdiagrams.html#column-constraint > > I can use an expression as the default value. Can I use max(something) to do > what I want and, if so, how ? I'm drawing a blank on the syntax and I can't > seem to google up an example. > > If it's not possible to do it this way, I assume I can use a TRIGGER to reset > the author field. I tried that and it worked but using a default constraint > would be more elegant. >
I am pretty sure you can't. Max(id) is not a standalone, column expression such as Sin() or Length(). It is an aggregate expression available inside a query, and not only that, you want to use Max(id) from a different table. Only way to get Max(id) is via 'SELECT Max(id) FROM table', which would render your table definition as CREATE TABLE books ( id INTEGER PRIMARY KEY, title TEXT, author INTEGER DEFAULT (SELECT Max(id) FROM authors) ); and that just ain't gonna work. A TRIGGER is the most elegant solution, and trivial to implement. > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
