Many thanks Simon, Keith and Richard for your attention and didactic
explanations.
Em 17-06-2018 14:37, Keith Medcalf escreveu:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
