Search code examples
mysqlforeign-keys

MySQL DB is ignoring foreign key constraints

I've got a really strange (and scary) issue: My mySQL DB is generally ignoring foreign key constraints!

All my tables are InnoDB and I'm creating them with liquibase like this:

<addForeignKeyConstraint baseColumnNames="user_account_id"
    baseTableName="account_transaction"
    constraintName="fk_account_transaction_user_account_id"
    referencedColumnNames="id"
    referencedTableName="user_account"/>

The foreign keys are created fine. In HeidiSQL I see them like this: heidiSQL DB Management

I also checked for FOREIGN_KEY_CHECKS and I've set it to 1 (SET FOREIGN_KEY_CHECKS=1;)

All my ID columns have the data type BIGINT (dunno if that matters).

What is also worth to mention: The following statement

  SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();

returns a bunch of constraints, but no foreign key constraints... When I execute the same statement on my local mySQL DB, it returns all the constraints INCLUDING foreign key constraints. I can't figure out what's the difference between them though...

Any help is very much appreciated :)

@Update: So the hint with MyISAM and InnoDB did the trick. I did investigate that before. However, for some reason my tables are InnoDB, but my DB is MyISAM. I'm gonna make further investigations on that :) enter image description here

Solution

  • As Bill Karwin notes in his comment, FOREIGN KEY constraints are ignored if the table is not InnoDB.

    Demonstration:

    CREATE TABLE foo_p
( id BIGINT UNSIGNED PRIMARY KEY
) ENGINE=MYISAM
;  

CREATE TABLE foo_c
( id   BIGINT UNSIGNED PRIMARY KEY
, p_id BIGINT UNSIGNED
) ENGINE=MYISAM
;

    Add a foreign key constraint: 

    ALTER TABLE foo_c 
  ADD CONSTRAINT fk_foo_c_foo_p
  FOREIGN KEY (id) REFERENCES foo_p (id)
  ON DELETE NO ACTION ON UPDATE NO ACTION
;

    MySQL creates an index but not a foreign key.

    Change storage engine to InnoDB does not create foreign keys:

    ALTER TABLE foo_p ENGINE=INNODB ;

ALTER TABLE foo_c ENGINE=INNODB ;

    Use SHOW CREATE TABLE foo_c to see table definition:

    CREATE TABLE `foo_c` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `p_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_foo_c_foo_p` (`id`)
) ENGINE=INNODB

    There's an index (converted from the MyISAM definition) but no foreign key.

    To add a foreign key:

    ALTER TABLE foo_c 
  DROP KEY fk_foo_c_foo_p  
, ADD CONSTRAINT fk_foo_c_foo_p
  FOREIGN KEY (p_id) REFERENCES foo_p (id)
  ON DELETE NO ACTION ON UPDATE NO ACTION
;

    A SHOW CREATE TABLE shows the FOREIGN KEY constraint, as well as the index that was automatically created:

    CREATE TABLE `foo_c` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `p_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_foo_c_foo_p` (`p_id`),
  CONSTRAINT `fk_foo_c_foo_p` FOREIGN KEY (`p_id`) REFERENCES `foo_p` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB