CODEWITHSUNDEEP
sqldatabasepostgresqlforeign-keysentity
d789w
d789w

Reputation: 377

Using the same primary keys across multiple tables

I'm new to creating a database from scratch in postgres.

For a user signing up to a website, the two entities are 'user' (their details) and 'user_pswrd' (to store the users password - which would eventually be encrypted).

My question is, should I create the same Primary Key in both tables:

CREATE TABLE user (user_name VARCHAR PRIMARY KEY,
                   first_name VARCHAR,
                   surname VARCHAR,
                   email VARCHAR);

CREATE TABLE user_pswrd (user_name VARCHAR PRIMARY KEY,
                         pswrd VARCHAR)

or a Primary Key in 'user' and just reference a Foreign Key in 'user_pswrd':

CREATE TABLE user (user_name VARCHAR PRIMARY KEY,
                   first_name VARCHAR,
                   surname VARCHAR,
                   email VARCHAR);

CREATE TABLE user_pswrd (pswrd VARCHAR,
                         FOREIGN KEY(user_name) REFERENCES user(user_name))

If none of these are correct, any advice will be appreciated.

Upvotes: 0

Views: 701

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You have two separate tables. You should have automatic primary keys and use that to connect them:

CREATE TABLE users (
    user_id int generated always as identity primary key,  -- in older versions, "serial"
    user_name VARCHAR unique,
    first_name VARCHAR,
    surname VARCHAR,
    email VARCHAR
);

CREATE TABLE user_password (
    user_id int primary key,
    password varchar,
    foreign key (user_id) references users(user_id)
 );

Notes:

  • There is no reason to store the password in a separate table, unless you have a business reason for doing so. This appears to be a 1-1 relationship.
  • You should NEVER be storing a plaintext password in your application. In fact, your application should never even see such a password. The encryption should be on the client side.
  • I'm uncomfortable with leaving the lengths off of varchar, but Postgres supports that.
  • There is no reason to corrupt the name of something like "password". Just type the whole word. In the modern world, there are no advantages to using "pswrd".

Upvotes: 1

Related Questions