On 6/17/18 12:55 PM, Markos wrote: > Good Morning, > > I am a beginner in database and I'm developing a Database (with > Tcl/Tk) to control the loans of books in a reading room. > > Some users will have administrator privileges (to make loans, register > other users etc). > > I am creating a table to store login and password to be used in the > authentication of the admin users (admin_user). > > Id_user is the primary key of the user table (all users). > > I want to avoid two administrators (admin_user) with the same login > but for this I am in doubt if I put the two fields as primary key or > as unique: > > > CREATE TABLE admin_user ( > id_user integer, > login text NOT NULL, > password text NOT NULL, > admin_registration_date INTEGER NOT NULL, > id_super_admin INTEGER NOT NULL, > *PRIMARY KEY (id_user, login),* > *FOREIGN KEY(id_**user**) REFERENCES us**er**(id_us**er**)*); > > > or > > > CREATE TABLE admin_user ( > id_user integer, > login text NOT NULL, > password text NOT NULL, > admin_registration_date INTEGER NOT NULL, > id_super_admin INTEGER NOT NULL, > *UNIQUE (id_user, login),* > *FOREIGN KEY(id_**user**) REFERENCES us**er**(id_us**er**)*); > > What are the possible consequences of the two strategies and which > would be the most appropriate? > > Thank you, > > Markos One first comment, unless you INTEND that one id_user might be accessed via multiple login names, the id_user should have at least a UNIQUE constraint, and as others have commented, you really want this to be the primary key.
You likely also want that a given login name be unique (and as some have shown, perhaps unique even under case folding), so that would be a second independent constraint. UNIQUE(id_user, login) doesn't make each item listed unique, but says that there that there can be only one record for each unique combination. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
