CODEWITHSUNDEEP
postgresqlcreate-tableunique-constraintweak-entity
Samuel Barata
Samuel Barata

Reputation: 57

Weak Entity postgresql

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

[email protected], 1 
[email protected], 2
[email protected], 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)

Upvotes: 1

Views: 2106

Answers (1)

William Prigol Lopes
William Prigol Lopes

Reputation: 1889

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 ('[email protected]', 1);
    INSERT INTO proposta_de_correcao VALUES ('[email protected]', 2);
    INSERT INTO proposta_de_correcao VALUES ('[email protected]', 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.

Upvotes: 1

Related Questions