Understanding / mySQL aka tricking ForeignKey relationships in Django

Question

So I've inherited some django.

The mySQL table is simple enough where parent is NOT a FK relationship just the "Parent" id:

CREATE TABLE `Child` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24;

But then the originator did this..

class Child(models.Model):
    """Project Child information"""
    id = models.AutoField(primary_key=True)
    parent = models.ForeignKey(Parent)
    name = models.CharField(max_length=255)

    class Meta:
        managed = False

Admittedly I am NOT a SQL Jockey but I know that a "real" Foreign Key Relationship looks similar to this notice CONSTRAINT...

CREATE TABLE `Child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `child_63f17a16` (`parent_id`),
  CONSTRAINT `parent_id_refs_id_34923e1e` FOREIGN KEY (`parent_id`) REFERENCES `Parent` (`id`)
) ENGINE=InnoDB;

What I want to know is the following:

1. What problems could I expect to see by this "trickery".
2. While this appears to work - is it recommended or advised.
3. Would we be advised to modify the SQL to add in the constraint?

Thanks so much!

Hepcat

Answer 1

1. Not having an actual constraint might lead to broken references, invalid parents and other sorts of data inconsistencies. I am not a Django expert but I would venture a guess that in most cases Django will still handle the relations fine unless you purposefully add some invalid records.

2. Normally, if your RDBMS supports foreign key constraints, there is absolutely no reason not to use them, and it could potentially be considered a design flaw to ignore them.

3. You should consider adding the key constraints. Not only do they give your DBMS a good idea of how to optimize the queries, they also ensure consistency in your data. I am pretty sure Django has a setting somewhere that will automatically generate the SQL to add the key constraints when you run manage.py syncdb

For more information about why you should prefer foreign keys, you should read the MySQL Foreign Key Documentation

Most interestingly:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

Barra

This is really good info - thanks so much for your detailed response - I certainly agree with you on the problems of not having it. That said do you see issues with simply assigning it as a FK without it imposing those constraints? It certainly makes my queries simpler... – Hepcat

I'm not sure what you mean by not imposing the constraint. In InnoDB when you have FOREIGN KEY REFERENCES, it automatically creates and enforces existence constraints. You don't need a CONSTRAINT clause. – Barra

Poorly worded. Do you see any issue with defining it as a ForeignKey in Django and not imposing that at the db level ; notice managed=False – Hepcat

Django will not enforce the constraints, it only needs to know them so it can create the appropriate SQL and JOIN-tables. You will also probably suffer performance issues on JOINs unless you manually add indices to all foreign key fields in the schema. If you do go in that direction, you will need to ensure (at the view level) that you are inserting data with existing/valid foreign keys, otherwise you will have orphans and you will still run into consistency issues. That being said, Django will work. You can run a MyISAM Schema (no FKs); it just won't ensure that all the references are valid. – Barra

@Barra : Did not understand a part in your comment - "it only needs to know them so it can create the appropriate SQL and JOIN-tables". When will this happen ? What is the use case for this ? – Oteliaotero

Answer 2

Its supposed to be faster ... since you mysql doesn't check the constraint before adding a row in the child table. But with the foreign key, it would make your life easier since you can use the on update and on delete. I'd go with the constraint.

Moonfaced

It would not be faster unless you manually added indices to the foreign key field. If you're going to do that though, you might as well go all the way through with foreign keys. Either way with an index you will have one page I/O, and checking the constraint is also one page I/O. This means there might be a small (usually insignificant) drawback on insert, but you get to be 100% sure that there are no broken references in your tables. – Barra