[oracle] issue ORA-00001: unique constraint violated coming in INSERT/UPDATE

Error message looks like this

Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated

ORA-00001 occurs when: "a query tries to insert a "duplicate" row in a table". It makes an unique constraint to fail, consequently query fails and row is NOT added to the table."

Solution:

Find all columns used in unique_constraint, for instance column a, column b, column c, column d collectively creates unique_constraint and then find the record from source data which is duplicate, using following queries:

-- to find <<owner of the table>> and <<name of the table>> for unique_constraint

select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';

Then use Justin Cave's query (pasted below) to find all columns used in unique_constraint:

  SELECT column_name, position
  FROM all_cons_columns
  WHERE constraint_name = <<name of constraint from the error message>>
   AND owner           = <<owner of the table>>
   AND table_name      = <<name of the table>>

    -- to find duplicates

    select column a, column b, column c, column d
    from table
    group by column a, column b, column c, column d
    having count (<any one column used in constraint > ) > 1;

you can either delete that duplicate record from your source data (which was a select query in my particular case, as I experienced it with "Insert into select") or modify to make it unique or change the constraint.

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to hibernate

Hibernate Error executing DDL via JDBC Statement How does spring.jpa.hibernate.ddl-auto property exactly work in Spring? Error creating bean with name 'entityManagerFactory' defined in class path resource : Invocation of init method failed JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory Disable all Database related auto configuration in Spring Boot Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] HikariCP - connection is not available Hibernate-sequence doesn't exist How to find distinct rows with field in list using JPA and Spring? Spring Data JPA and Exists query

Examples related to transactions

sql try/catch rollback/commit - preventing erroneous commit after rollback Laravel: Using try...catch with DB::transaction() UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only Transaction marked as rollback only: How do I find the cause Transaction isolation levels relation with locks on table Spring transaction REQUIRED vs REQUIRES_NEW : Rollback Transaction When to use SELECT ... FOR UPDATE? Correct use of transactions in SQL Server MySQL : transaction within a stored procedure issue ORA-00001: unique constraint violated coming in INSERT/UPDATE