ERROR: there is no unique constraint matching given keys for referenced table "users"

Question

I have read through some of the similar questions and answers and I don't quit understand the problem. One of my current guess is that Potgres might not allow foreign key-ing of non-primary key.

user:

user registration, 1 user per row, 1 id per row(serial), unique email(primary key)

user_logins:

information about user logins, references user_id from user as foreign key, the rest of the columns together makes a composite primary key

---

CREATE TABLE users
(
    unique_email    TEXT NOT NULL,
    password        TEXT NOT NULL,
    user_id         SERIAL,
    created_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT LOCALTIMESTAMP,    

    CONSTRAINT emailPK
                    PRIMARY KEY(unique_email)
);


CREATE TABLE user_logins
(
    user_id         SERIAL,
    login_date      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT LOCALTIMESTAMP,
    login_ip        INET,

    CONSTRAINT user_idFK
                    FOREIGN KEY (user_id)
                    REFERENCES users(user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
    CONSTRAINT composit_PK_logins
                    PRIMARY KEY(user_id, login_date, login_ip)
);

Answer 1

user_id must have unique constraints in users table. Otherwise, it will have ambiguities.

In this query, I added UNIQUE constraint in user_id

CREATE TABLE users
(
    unique_email    TEXT NOT NULL,
    password        TEXT NOT NULL,
    user_id         SERIAL UNIQUE,
    created_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT LOCALTIMESTAMP,    

    CONSTRAINT emailPK
                    PRIMARY KEY(unique_email)
);