PostgreSQL: cascade delete on bidirectional references only for source column

I have tables variable and variable_variable. The last one holds 2 columns:

1. variable_id_from
2. variable_id_from

Look at the schema below:

If variable_variable has row with values {variable_id_from = 1, variable_id_to = 2}, it means that variable with id = 1 references to variable with id = 2.

Now I have a new business requirement:

> Suppose we have variable A. If there any other variables that reference to A (i.e. variable_variable has any row with value of {variable_id_to = A.id}), then deletion of A must be prohibited.

> Suppose we have variable B. If there are no variables that references B but in the same time B references any other variables, then deletion of B should proceed successfully and all references where {variable_id_from = B.id} has to be deleted as well.

I was thinking to create one simple constraint and with the cascade delete. Look at the SQL snippet below:

ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_variable_id_from_fkey
        FOREIGN KEY (variable_id_from) REFERENCES variable (id)
            ON DELETE CASCADE;

ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_variable_id_from_fkey
        FOREIGN KEY (variable_id_to) REFERENCES variable (id);

I thought it's to going to serve my needs exactly. But strangely, this test cases doesn't pass:

1. Create variable A
2. Create variable B
3. Make A reference B
4. Try to delete B
5. Expected: error during variable deletion. Actual: B and all corresponding links in variable_variable are successfully deleted.

That's strange. Seems like the variable_variable_variable_id_from_fkey is somehow triggered. Any ideas how to solve this problem?

Besides, there is also an important case. Variable might reference itself. So, the variable_variable table can contain row {variable_id_from = 1, variable_id_to = 1}. In this case, the deletion should also pass successfully and all the links has to be deleted by cascade.

P.S. I know I can perform the deletion on the application side but I'm considering this decision as the last resort. The entire database structure is much more complicated than I've shown. And cascade constraints are really helpful to keep code cleaner.

A forign key would with the addition on deleet cascade, delete also n the bridge table all records

A BEFORE DELETE TRIGGER will do the job, as it will check every deleted row, if there are records in variable_variable

see example

CREATE TABLE variable (id int, name varchar(10))

> status
> CREATE TABLE
>

INSERT INTO variable VALUES(1,'test'),(2,'test2')

> status
> INSERT 0 2
>

CREATE tABLe variable_variable (variable_id_from int,variable_id_to int)

> status
> CREATE TABLE
>

INSERT INTO variable_variable VALUEs (3,1)

> status
> INSERT 0 1
>

CREATE OR REPLACE FUNCTION DELETE_ONLY_()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
declare
  l_count integer;
begin 
  select count(*)
    into l_count
  from variable_variable WHERE variable_id_to = OLd.id;

  if l_count > 0 then 
    raise exception 'There are still records';
  end if; 
  return old;
end; 

$$

> status
> CREATE FUNCTION
>

CREATE TRIGGER trigger_name 
    BEFORE DELETE
   ON variable
   FOR EACH ROW
       EXECUTE PROCEDURE DELETE_ONLY_()

> status
> CREATE TRIGGER
>

DELETE FROM variable WHERE id = 1

> error
> ERROR: There are still records
> CONTEXT: PL/pgSQL function delete_only_() line 10 at RAISE
>

DELETE FROM variable WHERE id = 2

> status
> DELETE 1
>

SELECT * FROM variable

fiddle

Thanks everybody for proposed solutions. I managed to solve my problem. Here are the defined constraints:

-- If current variable is being referenced by another variable, restrict deletion
ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_variable_id_to_fkey
        FOREIGN KEY (variable_id_to) REFERENCES variable (id)
            ON DELETE RESTRICT;

-- If current variable reference other variable, delete its link by cascade
ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_variable_id_from_fkey
        FOREIGN KEY (variable_id_from) REFERENCES variable (id)
            ON DELETE CASCADE;

As I understood, PostgreSQL checks constraints in the order they were created. So, here is what happens:

1. If there are any references like Other Variable -> Current Variable, restrict deletion. Otherwise, go the next step.
2. If there are any references like Current Variable -> Other Variable, delete Current Variable and its links in the variable_variable table on cascade.

However, I had another problem not with constraints but with Hibernate. Here is the Variable entity definition:

@Entity
@Table(name = "variable")
@Getter
@Setter(PROTECTED)
@NoArgsConstructor(access = PROTECTED)
@DynamicUpdate
public class Variable {
    @EmbeddedId
    private VariableId id;

    @ManyToMany(fetch = LAZY)
    @JoinTable(
        name = "variable_variable",
        joinColumns = @JoinColumn(name = "variable_id_from"),
        inverseJoinColumns = @JoinColumn(name = "variable_id_to")
    )
    private Set<Variable> variables = new HashSet<>();

    @ManyToMany(fetch = LAZY)
    @JoinTable(
        name = "variable_variable",
        joinColumns = @JoinColumn(name = "variable_id_to", updatable = false, insertable = false),
        inverseJoinColumns = @JoinColumn(name = "variable_id_from", updatable = false, insertable = false)
    )
    // this collection is readonly and never updates
    private Set<Variable> inverseVariables = new HashSet<>();
    
    ...
}

I used to delete Variable with simple Spring Data JPA repository method delete(Variable variable). Actually, that are the queries that were generated:

delete from variable_variable where variable_id_from = ?
delete from variable_variable where variable_id_to = ?
delete from variable where id = ?

As far as I understood, the owning-side ManyToMany collections are always orphanRemoval = true. So, Hibernate will always delete ManyToMany links before removing the entity itself (correct me if I'm wrong). Therefore, the DB constraint meant nothing because Hibernate deleted all links prematurely.

For now I put native SQL query and marked delete and deleteById methods to throw UnsupportedOperationException so nobody call them accidentally. Anyway, I don't think that's a clear solution. Do you have any ideas how I can tell Hibernate not to delete ManyToMany links on the owner side?

The issue you are facing is that when you delete variable B, the foreign key constraint variable_variable_variable_id_from_fkey on variable_id_to is not triggered because it is not defined with ON DELETE CASCADE. This means that the rows in variable_variable that reference B are not deleted, and therefore, the deletion of B proceeds successfully.

To solve this problem, you can modify the foreign key constraint on variable_id_to to also have ON DELETE CASCADE:

ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_variable_id_to_fkey
        FOREIGN KEY (variable_id_to) REFERENCES variable (id)
            ON DELETE CASCADE;

With this modification, when you delete variable B, all rows in variable_variable that reference B (i.e., where variable_id_to is equal to B's id) will also be deleted. This will prevent the deletion of B if any other variables reference it.

To handle the case where a variable references itself, you can add a trigger to the variable table that checks if a variable is referencing itself and deletes the corresponding rows in variable_variable:

CREATE TRIGGER delete_self_reference
    BEFORE DELETE ON variable
    FOR EACH ROW
    BEGIN
        DELETE FROM variable_variable WHERE variable_id_from = OLD.id AND variable_id_to = OLD.id;
    END;

This trigger will be triggered before a row is deleted from the variable table. If the row being deleted has an id that appears in both columns of a row in variable_variable, then the corresponding row in variable_variable will be deleted. This will allow a variable to be deleted even if it references itself.

With these modifications, the foreign key constraints and the trigger, you should be able to satisfy your business requirements and keep your database structure clean.

think about it