Delete data with Circular Dependency in SQL
Tags: sql, postgressql
Published: 2023-05-09 05:18:26

# 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 : 

```sql
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 : 
```sql
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 : 
```sql
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 : 

2. 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.

```sql
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](https://stackoverflow.com/questions/38112379/disable-postgresql-foreign-key-checks-for-migrations)