On 30/06/2011, at 8:56 PM, Ian Hardingham wrote: > I have this table: > > tournamentParticipantTable > > id INTEGER PRIMARY KEY > user INTEGER > tournamentId INTEGER > > I'm obviously going to put an index on both user, tournamentId and > tournamentId, user
Why is it obvious? It will depend on what searches you are doing. As Igor mentioned, an index on a, b effectively includes an index on a, so you don't need to specify it separately. Not that (a, b) gives a different index to (b, a) for different purposes, so again, it depends on your searches as to what you need (maybe both, or neither). > - but as the relation is unique, I was wondering if I could in some way let > SQLite know that? You can do it like this: create table tournamentParticipantTable ( id integer primary key not null , user integer not null references user(id) on delete cascade , tournamentId integer not null references tournamentTable (id) on delete cascade , unique(user, tournamentId) ) ; which automatically creates the needed index for you too. Or you can leave out the unique constraint in the table and instead add an index: create unique index tournamentParticipantUniqueIndex on tournamentParticipantTable (user, tournamentId) > Also... it does seem weird that id is the primary key when I'll never > actually use it. The integer primary key is created whether or not you specify it in the create table syntax. By specifying it, you are just creating an alias to the built in rowid column. Since it doesn't create any extra overhead to specify it, and you might end up needing it later anyway, I always include it in the table definition. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
