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
