Criteria to define two fields as Primary Key or Unique

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
On 17 Jun 2018, at 5:55pm, Markos <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] 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 <[hidden email]> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Damon
In reply to this post by Markos
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Markos
In reply to this post by Keith Medcalf
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-
>> [hidden email]] 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users