Subject: Re: Primary keys in CLSQL view classes
From: (Rob Warnock)
Date: Sun, 15 Oct 2006 19:39:18 -0500
Newsgroups: comp.lang.lisp
Message-ID: <>
petere <> 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 <> 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 Warnock			<>
627 26th Avenue			<URL:>
San Mateo, CA 94403		(650)572-2607