Trying to make a UNIQUE KEY become a FOREIGN KEY, but it says error-CodePudding

I created this table. I want the user_id to be inserted without someone having to type it. Can someone help me? When I tried to create it it shows this error:

Why is the fk_id being compared to cpf?

CREATE TABLE code.went_to  
(
    user_id INTEGER,
    cpf VARCHAR(11) NOT NULL,
    cep VARCHAR(8) NOT NULL,
    date DATE,
    contact_id INTEGER,

    CONSTRAINT fk_id 
        FOREIGN KEY (user_id) REFERENCES code.user, 
    CONSTRAINT fk_cpf_user 
        FOREIGN KEY (cpf) REFERENCES code.user, 
    CONSTRAINT fk_cep_place 
        FOREIGN KEY (cep) REFERENCES code.place,
    CONSTRAINT fk_tipo_id 
        FOREIGN KEY (tipo_id) REFERENCES code.contact,
    EXCLUDE USING gist (cpf WITH =, cep WITH =, daterange(data, (data   interval '4 months')::date) WITH &&),
    EXCLUDE USING gist (cep WITH =, contact_id WITH =, daterange(data, (data   interval '1 months')::date) WITH &&) 
);

The table that user_id comes from:

CREATE TABLE code.user 
(
    user_id SERIAL NOT NULL,
    cpf VARCHAR(11) NOT NULL,
    name CHAR(75) NOT NULL,
    nick_name VARCHAR(15),

    CONSTRAINT pk_cpf PRIMARY KEY(cpf),
    CONSTRAINT un_id UNIQUE (id),
    CONSTRAINT un_nick_name UNIQUE (nick_name)
);

Sorry if its stated weird, my English is not the best. But essentially the question should be:

How could I import data (user_id) from one table (user) into another table (went_to) based on the primary key (cpf)?

CodePudding user response：

If you don't specify a target column, the references clause assumes the primary key of the target table. There is no matching on the name happening.

You need to include the column of the unique key in your foreign key definition:

CONSTRAINT fk_id FOREIGN KEY (user_id) REFERENCES code."user"(user_id)

To make things less confusing, I would also do that for the FK that references the PK

CONSTRAINT fk_cpf_user FOREIGN KEY (cpf) REFERENCES code."user"(cpf),