Entering data into the database gives the error "duplicate key value violates uniqueness restriction"

Asked

Viewed 963 times

0

I tried to enter the data, but it gives the error duplicate key value violates uniqueness restriction.
I’ve already put all the primary and secondary keys.

MSG of error:

rg.postgresql.util.Psqlexception: ERROR: duplicate key value violates the uniqueness restriction "usuario_pkey" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(Queryexecutorimpl.java:2455) at org.postgresql.core.v3.QueryExecutorImpl.processResults(Queryexecutorimpl.java:2155) at org.postgresql.core.v3.QueryExecutorImpl.execute(Queryexecutorimpl.java:288) at org.postgresql.jdbc.PgStatement.executeInternal(Pgstatement.java:430) at org.postgresql.jdbc.PgStatement.execute(Pgstatement.java:356) at org.postgresql.jdbc.PgStatement.executeWithFlags(Pgstatement.java:303) at org.postgresql.jdbc.PgStatement.executeCachedSql(Pgstatement.java:289) at org.postgresql.jdbc.PgStatement.executeWithFlags(Pgstatement.java:266) at org.postgresql.jdbc.PgStatement.executeUpdate(Pgstatement.java:246) at insercaoBD.main(insercaoBD.java:45)

CREATE TABLE public.usuario (
    login varchar(100) NOT NULL,
    nome_clo varchar(100) NOT NULL,
    cidade_natal varchar(100) NOT NULL,
    PRIMARY KEY (login)
);


CREATE TABLE public.bloqueados (
    p1 varchar(100) NOT NULL,
    p2 varchar(100) NOT NULL,
    motivos varchar(100) NOT NULL,
  b1 varchar(100) NOT NULL,
    PRIMARY KEY (p1, p2, motivos)
);


CREATE TABLE public.conhece (
    c01 varchar(100) NOT NULL,
    co2 varchar(100) NOT NULL,
    PRIMARY KEY (c01, co2)
);


CREATE TABLE public.gosta (
    login_g varchar(100) NOT NULL,
    rating integer NOT NULL,
    uri varchar(100) NOT NULL,
    PRIMARY KEY (login_g)
);


CREATE TABLE public.artista_musical (
    id integer NOT NULL,
    nome_artista varchar(100) NOT NULL,
    pais varchar(100) NOT NULL,
    genero varchar(100) NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE public.cantor (
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    PRIMARY KEY (c1, c2)
);


CREATE TABLE public.banda (
    b1 integer NOT NULL,
    PRIMARY KEY (b1)
);


CREATE TABLE public.musico (
    id integer NOT NULL,
    nome varchar(100) NOT NULL,
    estilo varchar(100) NOT NULL,
    d_nasc date NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE public.categoria (
    nome_cat varchar(100) NOT NULL,
    PRIMARY KEY (nome_cat)
);




CREATE TABLE public.subcategoria (
    s1 varchar(100) NOT NULL,
    PRIMARY KEY (s1)
);


CREATE TABLE public.filme (
    nome varchar(100) NOT NULL,
    id integer NOT NULL,
    dt_lan date NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE public.diretor_ (
    id integer NOT NULL,
    tel varchar(100) NOT NULL,
    endereco varchar(100) NOT NULL
);


CREATE TABLE public.ator_ (
    id integer NOT NULL,
    tel varchar(100) NOT NULL,
    endereco varchar(100) NOT NULL
);


CREATE TABLE public.gostaf (
    login_gf varchar(100) NOT NULL,
    rating integer NOT NULL,
    uri varchar(100) NOT NULL,
    PRIMARY KEY (login_gf)
);


CREATE TABLE public.bandapossuimusico (
    idb integer NOT NULL,
    idm integer NOT NULL
);

CREATE INDEX index_idb ON public.bandapossuimusico
    (idb);
CREATE INDEX index_idm ON public.bandapossuimusico
    (idm);


ALTER TABLE public.bloqueados ADD CONSTRAINT FK_bloqueados__p1 FOREIGN KEY (p1) REFERENCES public.usuario(login);

ALTER TABLE public.bloqueados ADD CONSTRAINT FK_bloqueados__p2 FOREIGN KEY (p2) REFERENCES public.usuario(login);

ALTER TABLE public.conhece ADD CONSTRAINT FK_conhece__c01 FOREIGN KEY (c01) REFERENCES public.usuario(login);

ALTER TABLE public.conhece ADD CONSTRAINT FK_conhece__co2 FOREIGN KEY (co2) REFERENCES public.usuario(login);

ALTER TABLE public.gosta ADD CONSTRAINT FK_gosta__login_g FOREIGN KEY (login_g) REFERENCES public.usuario(login);

ALTER TABLE public.cantor ADD CONSTRAINT FK_cantor__c1 FOREIGN KEY (c1) REFERENCES public.artista_musical(id);

ALTER TABLE public.cantor ADD CONSTRAINT FK_cantor__c2 FOREIGN KEY (c2) REFERENCES public.artista_musical(id);

ALTER TABLE public.banda ADD CONSTRAINT FK_banda__b1 FOREIGN KEY (b1) REFERENCES public.artista_musical(id);

ALTER TABLE public.subcategoria ADD CONSTRAINT FK_subcategoria__s1 FOREIGN KEY (s1) REFERENCES public.categoria(nome_cat);

ALTER TABLE public.gostaf ADD CONSTRAINT FK_gostaf__login_gf FOREIGN KEY (login_gf) REFERENCES public.usuario(login);

ALTER TABLE public.bandapossuimusico ADD CONSTRAINT FK_bandapossuimusico__idb FOREIGN KEY (idb) REFERENCES public.banda(b1);

ALTER TABLE public.bandapossuimusico ADD CONSTRAINT FK_bandapossuimusico__idm FOREIGN KEY (idm) REFERENCES public.musico(id);
postgresql

  • The error says: duplicar valor da chave viola a restrição de unicidade "usuario_pkey", you are generating duplicated value in this field.

  • but I referenced this field https://pastebin.com/KikWAkvy my SQL

  • Add the code to the question by clicking EDIT, this link is inaccessible to me.

  • I put the code in the question

  • 1

    @Katielencastro the question is not the reference, there seems to be no errors in your DDL. The point is that the INSERT in your java code is trying to insert two (or more) users with the same login (which is the primary key of the user table)

  • Obg is I’ve solved

  • If you run an update that changes the primary key of a given record being that in the clause "Where" there is a condition that is satisfied by more than one record in the table the update will try to change the 2 or more records at once generating this error.

Show 2 more comments

1 answer

0

From what you saw your bank looks ok. This error you are suffering this happening can happen in the following situations:

Insert

At the time of insertion into the table where you are duplicating a key that cannot be duplicated.

Select with more than one line

You can have a select that returns more than one line, and this value is not expected.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.