Introduction

Hello everyone, it has been a little while since my last article. And recently, I run out to an issue when locally testing some new features manually.

I added values to my database to test my features, but then I needed to remove them, and I did not want to drop and reseed my database for that. So I tried deleting them, but impossible I got an error near this one :

ERROR:  update or delete on table "test_1" violates foreign key constraint "test_2_test_1_id_fkey" on table "test_2"
DETAIL:  Key (id)=(1) is still referenced from table "test_2".
SQL state: 23503

It was the opportunity to write an article about deleting circular dependency in SQL and enhance my knowledge.

The issue

So what is the issue here? The thing is, when we created the tables I am talking about in the database, we created them with something more or less like this :

CREATE table test_1 (
    id integer primary key
);
CREATE TABLE test_2 (
    id integer primary key,
    test_1_id integer,
    foreign key (test_1_id) references test_1(id)
)
ALTER TABLE test_1 ADD COLUMN test_2_id integer;
ALTER TABLE test_1 ADD FOREIGN KEY (test_2_id) REFERENCES 
test_2(id)

What have we done here? We are saying that a network model is underlying but more critical.
Now we have a circular dependency between test_1 and test_2 tables.

So if we insert data like this :

INSERT INTO test_1 VALUES (1,NULL);
INSERT INTO test_2 VALUES (1,1);
UPDATE test_1 SET test_2_id = 1;

We cannot delete it anymore from table test_1 or test_2 because of the circular dependency.
And we will have the same error as I told you about.

ERROR:  update or delete on table "test_1" violates foreign key constraint "test_2_test_1_id_fkey" on table "test_2"
DETAIL:  Key (id)=(1) is still referenced from table "test_2".
SQL state: 23503

We won't be able to delete this data anymore. We can do several stuff to delete them.

Solutions

1. The One I use temporarily disabling the constraint on the foreign key.

If you do not want/need to touch your database schema and you are working locally, it can be a fast solution.

This will look something like this :

BEGIN;
-- Disable all the trigger
ALTER TABLE test_1 DISABLE TRIGGER ALL;
DELETE FROM test_1;
-- re-enable all the trigger
ALTER TABLE test_1 ENABLE TRIGGER ALL;
COMMIT;

As you can see, we disable the trigger; indeed, in PostgreSQL, referential Integrity is implemented thanks to triggers [1].

This solution is a one-off, and if it is an action that you are doing often, I do not recommend doing that. Moreover, do not forget to re-enable your t

What you can do instead is :

1. Changing a bit the schema and adding 'DELETE ON CASCADE' to the foreign key

If you can change your database schema, you can also add something that will be more future-proof and less risky.

To add the 'DELETE ON CASCADE', drop your current foreign key and recreate it with the option.

ALTER TABLE test_1 DROP CONSTRAINT test_2_test_1_id_fkey;
ALTER TABLE test_2 DROP CONSTRAINT test_1_test_2_id_fkey;
ALTER TABLE test_1 ADD FOREIGN KEY (test_2_id) REFERENCES 
test_2(id) ON DELETE CASCADE;
ALTER TABLE test_2 ADD FOREIGN KEY (test_1_id) REFERENCES 
test_1(id) ON DELETE CASCADE;

How is this solving our issue? This is quite essential SQL but the 'ON DELETE CASCADE' option will delete the dependent entities and the entity you are deleting.

Conclusion

As you can see, circular dependency does not force you to keep data forever in your databases.
In this article, we've seen two ways to delete these data:

• Disabling foreign constraints temporarily
• Modify the schemas and add an option to the foreign key.

Thanks for reading me!

REFERENCES :

1.StackOverflow Question