Hey guys, thank you all for the help. I need to look into foreign keys.
On 30/06/2011 13:31, Black, Michael (IS) wrote:
>
> You're getting close....if you don't use a field in a table you
> don't HAVE to create it.
>
> Also...if you want to make your database a bit more bullet proof you
> want foreign keys to help ensure you maintain the relationship between
> users and tournaments (otherwise you can accidentally delete something).
>
> If tournamentId is INTEGER already, then that's what you want for your
> primary key.
>
> Also note that names are NOT guaranteed to be unique so you either
> need some other moniker (like login) or don't enforce the unique
> constraint on that field. Depends on what kind of application you're
> doing.
>
> sqlite> pragma foreign_keys = on;
>
> sqlite> create table user(userid integer primary key
> autoincrement,name varchar, login varchar);
> sqlite> create unique index index1 on user(userid);
> sqlite> create unique index index2 on user(login);
>
> sqlite> create table tournamentParticipant(tournamentId integer
> primary key autoincrement,userid integer,foreign key(userid)
> references user(userid));
>
> Now some data
>
> sqlite> insert into user(name,login) values('John Smith','jsmith');
> sqlite> insert into user(name,login) values('James Smith','jsmith');
> Error: column login is not unique
> sqlite> insert into user(name,login) values('James Smith','jsmith2');
> sqlite> insert into tournamentParticipant(userid) values((select
> userid from user where login='jsmith'));
>
> sqlite> select login,tournamentId from user,tournamentParticipant
> where user.userid=tournamentParticipant.userid;
> login|tournamentId
> jsmith|1
>
> sqlite> delete from user where userid=1;
> Error: foreign key constraint failed
>
> There's more to foreign key support but there's lot's of info out
> there about it.
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
