What is this "_ifbk" constraint doing in my table?

Question

I'm working on a Rails web application with a MySQL database. I'm using migrations to modify the schema. I've just run into a problem wherein I'm getting an invalid record error on inserting a new record. The relevant tables:

users
id | name | email | ...

academic_records
id | scholar_id | gpa | ...

academic_records.scholar_id is a foreign key reference to users.id, but I haven't placed any other constraints on that table. The error looks like this:

Mysql::Error: Cannot add or update a child row: a foreign key constraint fails
 (`my_db`.`academic_records`, CONSTRAINT `academic_records_ibfk_1`
 FOREIGN KEY (`id`) REFERENCES `academic_records` (`id`) ON DELETE CASCADE): ...

I opened up the MySQL database using Sequel Pro and found this constraint:

CREATE TABLE `academic_records` (
  `gpa` varchar(10) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `index_academic_records_on_scholar_id` (`scholar_id`),
  CONSTRAINT `academic_records_ibfk_1` FOREIGN KEY (`id`)
    REFERENCES `academic_records` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=utf8;

I have no idea how it got there or what it does. Why would the table have a foreign key from its own ID to itself? Can I remove the constraint? If so, how? I could see why I would want to cascade deletes from users.id to academic_records.scholar_id, but as-is, it makes no sense to me.

Answer 1

This kind of thing is not possible through Rails migrations using the DSL ... however, that doesn't stop people from creating a foreign key manually, or using an execute("SQL HERE") in your migration.

As I am unfamiliar with MySQL's syntax your best reference is the MySQL documentation - particularly DROP CONSTRAINT.

Why it references itself ... I have no idea, time to ask your colleagues?

Answer 2

Check your migration files to see if the constraint was created there, or perhaps your project is using some plugin or RubyGem that does something with database constraints. If both of those areas turn up blanks then it must have been created by hand or by an external SQL script.

Rails doesn't generate or use database constraints to enforce model relationships. Database referential integrity is something you have to add yourself if you want it.

I don't see any reason why you can't drop this constraint using:

mysql> ALTER TABLE academic_records DROP FOREIGN KEY `academic_records_ibfk_1`;