CODEWITHSUNDEEP
mysqldjango
Anurag Rana
Anurag Rana

Reputation: 1466

foreign key constraint not letting delete row from table in django

I create below model in Django.

class ClientsModel(models.Model):
    sys_id = models.AutoField(primary_key=True, null=False, blank=True)
    tenant_sys_id = models.ForeignKey('tenant.TenantModel', on_delete = models.SET_NULL, null=True, blank=True)
    last_name = models.CharField(max_length=40, null=True, blank=True)
    # and few more rows.

Here tenant_sys_id is a foreign key. I set on_delete = models.SET_NULL, which means if I delete any entry from TenantModel (lets say entry1) then column tenant_sys_id in all rows having tenant_sys_id as entry1 should be set to null. I am right here?
If yes- Then here is next question. If I try to delete an entry from tenants table from mysql then it is throwing me below error.

mysql> delete from tenants where sys_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sanstha_db`.`clients`, CONSTRAINT `Clients_tenant_sys_id_id_52191b87_fk_Tenants_sys_id` FOREIGN KEY (`tenant_sys_id_id`) REFERENCES `tenants` (`sys_id`))

Here is the table structure.

Table: clients
Create Table: CREATE TABLE `clients` (
  `sys_id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(40) DEFAULT NULL,
  `first_name` varchar(40) NOT NULL,
  `middle_name` varchar(40) DEFAULT NULL,
  `display_name` varchar(140) NOT NULL,
  `is_business` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `is_vendor` tinyint(1) NOT NULL,
  `is_authorized_to_expense` tinyint(1) NOT NULL,
  `relation` bigint(20) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` bigint(20) DEFAULT NULL,
  `created_when` datetime DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `last_updated_when` datetime DEFAULT NULL,
  `last_updated_by` bigint(20) DEFAULT NULL,
  `notes` varchar(2048) DEFAULT NULL,
  `head_of_family_id` int(11) DEFAULT NULL,
  `tenant_sys_id_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sys_id`),
  KEY `Clients_head_of_family_id_01c17980_fk_Clients_sys_id` (`head_of_family_id`),
  KEY `Clients_tenant_sys_id_id_52191b87_fk_Tenants_sys_id` (`tenant_sys_id_id`),
  CONSTRAINT `Clients_head_of_family_id_01c17980_fk_Clients_sys_id` FOREIGN KEY (`head_of_family_id`) REFERENCES `clients` (`sys_id`),
  CONSTRAINT `Clients_tenant_sys_id_id_52191b87_fk_Tenants_sys_id` FOREIGN KEY (`tenant_sys_id_id`) REFERENCES `tenants` (`sys_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1

Please suggest me why it is not letting me delete the entry? what is the correct way to do it.

Upvotes: 0

Views: 4898

Answers (1)

Ankush Raghuvanshi
Ankush Raghuvanshi

Reputation: 1442

So here's the catch.

on_delete = models.SET_NULL will replace the value of that foreign key (which you deleted in the parent table) with null value.

But the above will be done by Django when say you write some logic in your views.py to delete some row of tenants models, as the on_delete = Models.SET_NULL is something defined in Django and not in MySQL.

So when you do that in your views.py, then Django interprets it, then makes the foreign keys null in the model ClientsModel and ONLY after that it deletes the row from the model tenants.

But if you do that from the another terminal by using the MySQL command line, then you are not interacting with Django and directly deleting the row from the table tenants. This way, the foreign keys in the model ClientsModel won't be set to null and hence you'll get the foreign key constraint error

And lastly, the answer to your last comment. You won't get this error if you delete a row from Django, irrespective of whether you use Django's ORM or raw SQL queries.

Upvotes: 4

Related Questions