A table with a composite Primary key, one of the fields with autoincrement

Im using a table constraint to create a composite primary key, I would like the id field to autoincrement, is this possible? or what are the alternatives?

   CREATE TABLE IF NOT EXISTS atable(开发者_运维技巧
        id          INTEGER NOT NULL, --I want to autoincrement this one
        name     TEXT NOT NULL,
        anotherCol  TEXT,

    PRIMARY KEY(id, name));

No, there's only one primary key: that's the composite of id and name.

If you mean that you want id to be the primary key, and name to be an indexed alternate key, I'd say that you should give name a unique constraint and make id the primary key.

Here's the link to the SQLite FAQ page where your question of how to autoincrement an integer primary key is #1. http://www.sqlite.org/faq.html#q1

Here's your SQL reworked a little:

 CREATE TABLE IF NOT EXISTS atable(
    id          INTEGER PRIMARY KEY, -- use NULL for this column on INSERT to autoinc
    name        TEXT NOT NULL,
    anotherCol  TEXT);

then create a unique index on NAME as suggested by duffymo and Kaleb.

It doesn't look to me that the OP want names to be unique. (But I could be wrong.) At any rate, you can

1. get an autoincrementing integer by using INTEGER PRIMARY KEY and inserting NULL into that column, and
2. declare a superkey by using a UNIQUE constraint on (id, name).

A superkey is just a candidate key (primary key in this case) plus one or more columns.

CREATE TABLE yourtable(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    CONSTRAINT superkey UNIQUE (id, name)
);

If you turn on foreign key support using PRAGMA foreign_keys = on;, the superkey can be the target of foreign key constraints in other tables. But I'm not certain that's what you were looking for.