CODEWITHSUNDEEP
postgresqlspring-bootspring-data-jpa
Merv
Merv

Reputation: 1149

ERROR: update or delete on table "tablename" violates foreign key constraint

I'm trying to delete the parent student or parent course and I get this error:

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "student" violates foreign key constraint "fkeyvuofq5vwdylcf78jar3mxol" on table "registration"

RegistrationId class is a composite key used in Registration class. I'm using Spring data jpa and spring boot.

What am I doing wrong? I know that putting cascadetype.all should also remove the children when the parent is deleted but it is giving me an error instead.

@Embeddable
public class RegistrationId implements Serializable {

  @JsonIgnoreProperties("notifications")
  @OneToOne(cascade=CascadeType.ALL)
  @JoinColumn(name = "student_pcn", referencedColumnName="pcn")
  private Student student;

  @JsonIgnoreProperties({"teachers", "states", "reviews"})
  @OneToOne(cascade=CascadeType.ALL)
  @JoinColumn(name = "course_code", referencedColumnName="code")
  private Course course;


Registration class

@Entity(name = "Registration")
@Table(name = "registration")
public class Registration {

@EmbeddedId
private RegistrationId id;

Upvotes: 42

Views: 114600

Answers (5)

Herbert
Herbert

Reputation: 5645

I had this issue in a different context, e.g. postgresql with django, not java/springs, but this might apply as well. I was dealing with pages with layouts that contained regions that in turn contained (ocr) texts, both layouts and OCRs generated by neural networks and algorithms and could have been ran under different settings, so I wanted to delete duplicates and keep the latest run of a setting on a page.

Due to a bug the algorithms were ran too often and I wanted to clean up the superfluous layouts that slowed down everything. So this solution isn't a deploy maintenance action, rather a one off. Still I hope it helps!

The CASCADE DELETE didn't work for me, probably because the layourregions were nested, i.e. page border -> paragraph -> line.

I went for soft-deletes because deleting the OCR's in one query with WHERE id IN or WHERE EXISTS were terribly slow.

I solved it by soft deletes first and hard deletes later, that is add a column delete, fill it, and then delete those.

Soft delete

-- add the column, choose a name that's not already there for a different purpose.
ALTER TABLE IF EXISTS public.pr_layout
    ADD COLUMN delete boolean DEFAULT False;

-- set to true for those that are not the newest, i.e. rank > 1
UPDATE pr_layout SET delete=true WHERE id IN (
    SELECT id FROM ( 
        SELECT settings_id, page_id, id, RANK() OVER (
            PARTITION BY settings_id, page_id
            ORDER BY created_on DESC
        ) rank FROM pr_layout
    ) AS ranked_layouts WHERE rank > 1
)

--same twice
ALTER TABLE IF EXISTS public.pr_layoutregion
    ADD COLUMN delete boolean DEFAULT False;
    
UPDATE pr_layoutregion SET delete=true WHERE layout_id IN (
    SELECT id FROM pr_layout WHERE pr_layout.delete)


ALTER TABLE IF EXISTS public.pr_ocr
    ADD COLUMN delete boolean DEFAULT False;
    
UPDATE pr_ocr SET delete=true WHERE region_id IN (
    SELECT id FROM pr_layoutregion WHERE pr_layoutregion.delete)

Sanity checks

Checked how much was kept and deleted as a sanity check:

(SELECT 'layout delete', COUNT(*) FROM pr_layout WHERE delete) UNION (SELECT 'layout keep', COUNT(*) FROM pr_layout WHERE NOT delete) UNION (SELECT 'region delete', COUNT(*) FROM pr_layoutregion WHERE delete) UNION (SELECT 'region keep', COUNT(*) FROM pr_layoutregion WHERE NOT delete) UNION (SELECT 'ocr delete', COUNT(*) FROM pr_ocr WHERE delete) UNION (SELECT 'ocr keep', COUNT(*) FROM pr_ocr WHERE NOT delete)

Make sure to add a dummy column with the count's name as I did, because postgresql orders resulting records as it likes.

Hard delete

DELETE FROM pr_ocr where delete;
DELETE FROM pr_layoutregion WHERE delete;
DELETE FROM pr_layout WHERE delete;

If you're anxious like me you'll run a second sanity check to see if everything was deleted.

Clean up columns

I used pgadmin4 for that and don't dare to guess the sql (and maybe mess up your db). So left as an exercise to the reader. (Which is my advice anyway, do tricky stuff with an interface before you hurt yourself).

Upvotes: 1

Narges Hashemi
Narges Hashemi

Reputation: 11

Try this method too. I got the answer with this method,This is just a test to remove. Pay attention to the cascade!

MyUser Entity

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstname;
private String lastname;
private String mobile;
@Column(unique = true)
private String email;
private Long date;
private LocalTime localiime;
private LocalTime localiimeend;

@ManyToOne(fetch = FetchType.LAZY,cascade = CascadeType.MERGE)
@JoinColumn(foreignKey = @ForeignKey(name = "role_fk"))
private Role role;

Role Entity

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String description;

@OneToMany(mappedBy = "role", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<MyUser> users;


@ManyToOne (fetch = FetchType.LAZY,cascade = CascadeType.MERGE)
@JoinColumn(foreignKey = @ForeignKey(name = "rolecat_fk"))
private rolecat rolecat;

rolecat Entity

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;

@OneToMany(mappedBy = "rolecat", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<Role> roles;

Upvotes: 1

Merv
Merv

Reputation: 1149

I made it work by using hibernate @OnDelete annotation. Some how the JPA.persistence CascadeTypes were not working. They had no effect for whichever I chose.

Just like below. Now I can remove the parent Student or the parent Course and all children(Registrations) are deleted with them.

@Embeddable
public class RegistrationId implements Serializable {

    @JsonIgnoreProperties("notifications")
    @OnDelete(action = OnDeleteAction.CASCADE)
    @OneToOne
    @JoinColumn(name = "student_pcn", referencedColumnName="pcn")
    private Student student;

    @JsonIgnoreProperties({"teachers", "states", "reviews"})
    @OnDelete(action = OnDeleteAction.CASCADE)
    @OneToOne
    @JoinColumn(name = "course_code", referencedColumnName="code")
    private Course course;

Upvotes: 20

Moshe Arad
Moshe Arad

Reputation: 3733

When you're using a relational DB, you are setting entities with relationships between these entities.

The error that you're getting means that:

You're trying to delete a record that its primary key is functioning as a foreign key in another table, thus you can't delete it.

In order to delete that record, first, delete the record with the foreign key, and then delete the original that you wanted to delete.

Upvotes: 42

JoshKopen
JoshKopen

Reputation: 960

Foreign keys guarantee that an entry will exist in another table. This is a way of ensuring data integrity. SQL will never allow you to delete this entry while it still deletes in the other table. Either (1) this is letting you know you would have made a grave mistake by deleting this thing which is required or (2) you would like to put in a cascading delete so that not only is this entry deleted but so is what is supposed to be referencing it in the other table. Information on cascading deletes can be found here and written fairly easily (https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php). If neither of these two descriptions fits you, evaluate why your foreign key relationship exists in the first place because it probably should not.

Upvotes: 6

Related Questions