Weak Entity postgresql

Question

I want to create a table with primary key email,nro being nro a sequential number for each email ex:

user1@e.com, 1 
user1@e.com, 2
user2@e.com, 1 

create table proposta_de_correcao(
    email varchar(255) not null,
    nro serial not null,
    unique(nro,email),
    PRIMARY KEY(nro, email),
    FOREIGN KEY (email) REFERENCES Utilizador(email),
);

But I get the following error:

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

I have already tried:

unique(nro,email)
contraint keys unique(nro,email)

Answer 1

Two things here, to help with your problem:

1. Why nro is serial if is not a sequential field in your database? serial type is used when you want that the field be incremented automatically (as in single integer primary keys). Maybe is better that you put an int type here.

2. You have in your table no unique constraints. If you create a fiddle, you can see that code runs fine:

    CREATE TABLE proposta_de_correcao(
        email VARCHAR(255) not null,
        nro SERIAL not null,
        UNIQUE(nro, email),
        PRIMARY KEY(nro, email)
        -- FOREIGN KEY (email) REFERENCES Utilizador(email)
    );

    INSERT INTO proposta_de_correcao VALUES ('user1@e.com', 1);
    INSERT INTO proposta_de_correcao VALUES ('user1@e.com', 2);
    INSERT INTO proposta_de_correcao VALUES ('user2@e.com', 1);

So, I can conclude that when you want to add the constraint, your database already have duplicated data in those two columns. Try to create in a test database the data mentioned above and you will see that runs perfectly.

I just removed the foreign key constraint to allow to run the code as we don't have the referenced table in example and we can consider that the referenced table don't have influence on the problem cited on answer.

Here's the fiddle.