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
________________________________
From: [email protected] [[email protected]] on
behalf of Ian Hardingham [[email protected]]
Sent: Thursday, June 30, 2011 5:56 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Defining a relationship as unique
Hey guys.
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 - but as the relation is unique, I was wondering if I
could in some way let SQLite know that?
Also... it does seem weird that id is the primary key when I'll never
actually use it.
Thanks,
Ian
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
