When Inserting Data Into a Table, An Error Is Reported: "duplicate key value violates unique constraint "%s""

Symptom

When inserting data into a table, an error is reported: "duplicate key value violates unique constraint "%s"".

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE TABLE films (
code        char(5) PRIMARY KEY,
title       varchar(40) NOT NULL,
did         integer NOT NULL,
date_prod   date,
kind        varchar(10),
len         interval hour to minute
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "films_pkey" for table "films"
CREATE TABLE

INSERT INTO films VALUES ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
ERROR:  dn_6003_6004: duplicate key value violates unique constraint "films_pkey"
DETAIL:  Key (code)=(UA502) already exists.

Possible Causes

The table films is created with primary key constraint, and the code column is declared as the primary key. Therefore, the code column can contain only unique non-null values. In addition, the films_pkey index is created.

The code column of the inserted table contains the value UA502 that is already in the primary key column. As a result, an error is reported.

Handling Procedure

• Method 1: Check for data conflicts and modify the inserted data. For example, change the duplicate value UA502 to UA509.

  1 2	INSERT INTO films VALUES ('UA509', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); INSERT 0 1

• Method 2: Delete the primary key constraint of the table films.

  1 2 3 4

  ALTER TABLE films DROP CONSTRAINT films_pkey; ALTER TABLE INSERT INTO films VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); INSERT 0 1