Also note that you probably want your application to store the password as a salted-hash, and not as a plain-text password. Otherwise someone could look up the passwords with a text editor ...
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Sunday, 17 June, 2018 11:30 >To: SQLite mailing list >Subject: Re: [sqlite] Criteria to define two fields as Primary Key or >Unique > >On 17 Jun 2018, at 5:55pm, Markos <mar...@c2o.pro.br> wrote: > >> 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: > >Your ideas both have different advantages, but are not the normal way >to do things. Try this instead: > >CREATE TABLE user ( > id INTEGER PRIMARY KEY, > login text COLLATE NOCASE NOT NULL UNIQUE, > password text NOT NULL, > admin INTEGER DEFAULT 0, > admin_registration_date INTEGER NOT NULL); > >The idea is that you have just one account table. In that you have >everyone, whether they're superadmin, admin or mundane users. You >just have a field saying what kind of account the row represents. >The 'UNIQUE' keyword for the 'login' field has SQLite create its own >private index so it can check for uniqueness, and if in other places >it needs to look up a login name it will use that index. > >In the above I have just two values for the admin field. users are >'0' admins are '1' (which SQLite interprets as meaning TRUE). > >But you seem to have a superadmin status (presumably you). So you >might prefer '0' for superadmin, '1' for admin, '2' for normal users. >Or some other system that suits you. Maybe even store the words >'user', 'admin', 'superadmin'. > >The use of the 'id' field as INTEGER PRIMARY KEY is a fundamental >part of the way SQL tables are often used. Every row has an INTEGER >key, assigned by the SQL engine (you don't set it yourself, >automatically incrementing values are set for you). You never change >those values. And when you need to refer to that entry in other >tables (e.g. a foreign key) you use the correct 'id' value, not >someone's login name. > >See FAQ number 1 in > ><https://sqlite.org/faq.html#q1> > >Simon. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
