On 29/03/2011, at 12:59 AM, Sam Carleton wrote: > The system calls for an associative table, a table with two foriegn > keys to two other tables, allowing for a many to many relationship. > Is there any way to make the primary key be both the columns?
Yes, you can define a primary key on two columns: CREATE TABLE Invoice_Item_Favorite ( Invoice_Item_Id INTEGER , FavoriteId INTEGER , primary key (Invoice_Item_Id, FavoriteId) , FOREIGN KEY(Invoice_Item_Id) REFERENCES Invoice_Item(Invoice_Item_Id) , FOREIGN KEY(FavoriteId) REFERENCES Favorite(FavoriteId) ); The primary key constraint does the same job as the unique constraint, so I would instead use the build in integer primary key as the primary key and define anything else as unique, like this: CREATE TABLE Invoice_Item_Favorite ( ID integer primary key , Invoice_Item_Id INTEGER , FavoriteId INTEGER , unique (Invoice_Item_Id, FavoriteId) , FOREIGN KEY(Invoice_Item_Id) REFERENCES Invoice_Item(Invoice_Item_Id) , FOREIGN KEY(FavoriteId) REFERENCES Favorite(FavoriteId) ); This doesn't use any extra disk space or memory, since it just aliases the build in row_id that all tables have. The advantage is that you can choose to refer to the ID column as a single column unique identifier. You can also use the multi-column unique constraint, but this requires more complex syntax (eg in where statements). Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
