petere <peter.eddy@gmail.com> wrote:
+---------------
| Maverik wrote:
| > Apart from this one may have strange problems, when trying to define
| > primary keys via :db-constraints on a certain field (e.g., to make it
| > autoincrementable unique identifier). ...
| 
| I've worked around similar problems with clsql and postgresql. I wasn't
| able to get the auto-unique ID postgresql feature to work (SERIAL) with
| clsql, so I just defined a table that contains the next ID for each
| type that uses one, and I get that and then increment it each time I
| create something. A little bit of a pain, but not too bad.
+---------------

I use PG <http://www.cliki.net/Pg> to talk to PostgreSQL [which I
prefer over MySQL because of better transaction support and row-level
concurrency/locking], and have had no problems using columns with
auto-generated sequence numbers, using PostgreSQL's SEQUENCE type,
e.g. for a table of "comments":

    CREATE SEQUENCE comment_seq CACHE 1;

    CREATE TABLE comments (
	comment_id  integer PRIMARY KEY DEFAULT nextval('comment_seq'),
	...[other columns]...
    );

You just have to make sure that when you INSERT new entries into
"comments" that you *don't* specify the "comment_id" attribute.

By the way, the other reason I used PG instead of CLSQL was that
the databases my apps use are often accessed from *other* languages
than just CL (e.g., Perl, C, PHP, etc.), so having to construct
the textual representation of SELECTs, etc., myself is actually
an slight advantage when trying to communicate/negotiate with
people writing in those other languages.


-Rob

-----
Rob Warnock			<rpw3@rpw3.org>
627 26th Avenue			<URL:http://rpw3.org/>
San Mateo, CA 94403		(650)572-2607