Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Markos]

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  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



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

Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Richard Damon]

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

Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Keith Medcalf]

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  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
>
>
>
>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

Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Simon Slavin]

On 17 Jun 2018, at 5:55pm, Markos  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



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Criteria to define two fields as Primary Key or Unique

[Markos]

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users