I can't get foreign keys to work in MySQL

Liquibase Development
1

Hi,
I can’t see much difference between these 2 SQL scripts, but the one created by Liquibase adds UNIQUE constraints after the table is created instead of doing it as part of the CREATE TABLE statement.  For some reason, when I run the Liquibase version on MySQL the foreign keys don’t get created, even though I see an ALTER TABLE statement that adds the foreign key.

Here is the version that Liquibase creates:

    -- ********************************************************************* -- Update Database Script -- ********************************************************************* -- Change Log: /tmp/changelog.xml -- Ran at: 29/06/10 12:53 PM -- Against: root@localhost@jdbc:mysql://localhost:3306/TEST_SCHEMA -- LiquiBase version: 2.0-rc2-SNAPSHOT -- *********************************************************************

    – Changeset /tmp/changelog.xml::1277830437442-1::root (generated)::(Checksum: 2:32bfb63aab569f7cff744d807770ad27)
    CREATE TABLE TEST_SCHEMA.NOT_NULL_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL, INTEGER_ID INT NOT NULL, INTEGER_UNIQ INT NOT NULL, BIGINT_ BIGINT NOT NULL, BLOB_1MB BLOB NOT NULL, CHAR_1 CHARACTER(1) NOT NULL, CLOB_1MB TEXT NOT NULL, DATE_ DATE NOT NULL, DECIMAL_5_0 DECIMAL(5,0) NOT NULL, DOUBLE_ DOUBLE NOT NULL, INTEGER_ INT NOT NULL, REAL_ DOUBLE NOT NULL, SMALLINT_ SMALLINT NOT NULL, TIME_ TIME NOT NULL, TIMESTAMP_ DATETIME NOT NULL, VARCHAR_50 VARCHAR(50) NOT NULL, CONSTRAINT PK_NOT_NULL_TBL PRIMARY KEY (INTEGER_PK));

    – Changeset /tmp/changelog.xml::1277830437442-2::root (generated)::(Checksum: 2:74fc205b8cef2b6c47316e6226499e9f)
    CREATE TABLE TEST_SCHEMA.NULL_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL, INTEGER_ID INT NOT NULL, INTEGER_UNIQ INT NOT NULL, BIGINT_ BIGINT, BLOB_1MB BLOB, CHAR_1 CHARACTER(1), CLOB_1MB TEXT, DATE_ DATE, DECIMAL_5_0 DECIMAL(5,0), DOUBLE_ DOUBLE, INTEGER_ INT, REAL_ DOUBLE, SMALLINT_ SMALLINT, TIME_ TIME, TIMESTAMP_ DATETIME, VARCHAR_50 VARCHAR(50), CONSTRAINT PK_NULL_TBL PRIMARY KEY (INTEGER_PK));

    – Changeset /tmp/changelog.xml::1277830437442-3::root (generated)::(Checksum: 2:9115beeb6d8eeaa1daa6f281e2325a5a)
    CREATE TABLE TEST_SCHEMA.NULL_WITH_DEFAULT_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL, INTEGER_ID INT NOT NULL, INTEGER_UNIQ INT NOT NULL, BIGINT_ BIGINT DEFAULT 1, CHAR_1 CHARACTER(1) DEFAULT ‘a’, CLOB_1MB TEXT, DATE_ DATE, DECIMAL_5_5 DECIMAL(5,5) DEFAULT 0, DOUBLE_ DOUBLE DEFAULT 3.14, INTEGER_ INT DEFAULT 1, REAL_ DOUBLE DEFAULT 3.14, SMALLINT_ SMALLINT DEFAULT 1, TIME_ TIME, TIMESTAMP_ DATETIME, VARCHAR_50 VARCHAR(50) DEFAULT ‘abc123’, CONSTRAINT PK_NULL_WITH_DEFAU PRIMARY KEY (INTEGER_PK));

    – Changeset /tmp/changelog.xml::1277830437442-4::root (generated)::(Checksum: 2:f1d39d91e42b5c298fb65ca6b7887acf)
    CREATE TABLE TEST_SCHEMA.SMALL_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL, INTEGER_UNIQ INT NOT NULL, INTEGER_ INT, CONSTRAINT PK_SMALL_TBL PRIMARY KEY (INTEGER_PK));

    – Changeset /tmp/changelog.xml::1277830437442-5::root (generated)::(Checksum: 2:53fc6636db9cc10bea2e74c91d9243a6)
    CREATE TABLE TEST_SCHEMA.SMALL_TBL_NO_PK (INTEGER_ INT NOT NULL);

    – Changeset /tmp/changelog.xml::1277830437442-6::root (generated)::(Checksum: 2:2be4097154489e3cbd40e5f78c65cb13)
    ALTER TABLE TEST_SCHEMA.NOT_NULL_TBL ADD CONSTRAINT UNIQ_NOT_NULL_TBL UNIQUE (INTEGER_UNIQ);

    – Changeset /tmp/changelog.xml::1277830437442-7::root (generated)::(Checksum: 2:e8eb13206370d46b46f034c59c3d9dae)
    ALTER TABLE TEST_SCHEMA.NULL_TBL ADD CONSTRAINT UNIQ_NULL_TBL UNIQUE (INTEGER_UNIQ);

    – Changeset /tmp/changelog.xml::1277830437442-8::root (generated)::(Checksum: 2:985438b9e065cdb8f8939ffb60729f6d)
    ALTER TABLE TEST_SCHEMA.NULL_WITH_DEFAULT_TBL ADD CONSTRAINT UNIQ_NULL_WITH_DEF UNIQUE (INTEGER_UNIQ);

    – Changeset /tmp/changelog.xml::1277830437442-9::root (generated)::(Checksum: 2:11d0cfe20bd704c97b75372003a9ae64)
    ALTER TABLE TEST_SCHEMA.SMALL_TBL ADD CONSTRAINT UNIQ_SMALL_TBL_1 UNIQUE (INTEGER_UNIQ);

    – Changeset /tmp/changelog.xml::1277830437442-10::root (generated)::(Checksum: 2:f44ee04280a0c1625a03b0dca3412b82)
    ALTER TABLE TEST_SCHEMA.NULL_TBL ADD CONSTRAINT FK_0001 FOREIGN KEY (INTEGER_UNIQ) REFERENCES TEST_SCHEMA.NOT_NULL_TBL(INTEGER_UNIQ) ON UPDATE NO ACTION ON DELETE NO ACTION;

    – Changeset /tmp/changelog.xml::1277830437442-11::root (generated)::(Checksum: 2:b57ea876519889039c55be27937abea0)
    ALTER TABLE TEST_SCHEMA.NULL_WITH_DEFAULT_TBL ADD CONSTRAINT FK_0002 FOREIGN KEY (INTEGER_UNIQ) REFERENCES TEST_SCHEMA.NOT_NULL_TBL(INTEGER_UNIQ) ON UPDATE NO ACTION ON DELETE NO ACTION;

    – Changeset /tmp/changelog.xml::1277830437442-12::root (generated)::(Checksum: 2:98c847005ee3cb97df4794bd5c8e62ad)
    CREATE INDEX TEST_INDEX2 ON TEST_SCHEMA.NOT_NULL_TBL(INTEGER_);

    – Changeset /tmp/changelog.xml::1277830437442-13::root (generated)::(Checksum: 2:a9d02d3d239ee49d27f58001820e1710)
    CREATE INDEX TEST_INDEX1 ON TEST_SCHEMA.NULL_TBL(INTEGER_);

    – Changeset /tmp/changelog.xml::1277830437442-14::root (generated)::(Checksum: 2:8dfc4e1891c8ebeb90a8c7eecaf34040)
    CREATE UNIQUE INDEX TEST_INDEX3 ON TEST_SCHEMA.NULL_WITH_DEFAULT_TBL(BIGINT_, TIME_);

    – Changeset /tmp/changelog.xml::1277830437442-15::root (generated)::(Checksum: 2:920c8ab469db1bd68adc8ab5c1f0bf45)
    CREATE VIEW TEST_SCHEMA.NULL_AND_NOT_NULL AS SELECT TEST_SCHEMA.NULL_TBL.INTEGER_PK AS INTEGER_PK,TEST_SCHEMA.NULL_TBL.BIGINT_ AS BIGINT_,TEST_SCHEMA.NULL_TBL.BLOB_1MB AS BLOB_1MB,TEST_SCHEMA.NULL_TBL.CHAR_1 AS CHAR_1,TEST_SCHEMA.NULL_TBL.CLOB_1MB AS CLOB_1MB,TEST_SCHEMA.NULL_TBL.DATE_ AS DATE_,TEST_SCHEMA.NULL_TBL.DECIMAL_5_0 AS DECIMAL_5_0,TEST_SCHEMA.NULL_TBL.DOUBLE_ AS DOUBLE_,TEST_SCHEMA.NULL_TBL.INTEGER_ AS INTEGER_,TEST_SCHEMA.NULL_TBL.REAL_ AS REAL_,TEST_SCHEMA.NULL_TBL.SMALLINT_ AS SMALLINT_,TEST_SCHEMA.NULL_TBL.TIME_ AS TIME_,TEST_SCHEMA.NULL_TBL.TIMESTAMP_ AS TIMESTAMP_,TEST_SCHEMA.NULL_TBL.VARCHAR_50 AS VARCHAR_50 FROM TEST_SCHEMA.NULL_TBL UNION ALL SELECT TEST_SCHEMA.NOT_NULL_TBL.INTEGER_PK AS INTEGER_PK,TEST_SCHEMA.NOT_NULL_TBL.BIGINT_ AS BIGINT_,TEST_SCHEMA.NOT_NULL_TBL.BLOB_1MB AS BLOB_1MB,TEST_SCHEMA.NOT_NULL_TBL.CHAR_1 AS CHAR_1,TEST_SCHEMA.NOT_NULL_TBL.CLOB_1MB AS CLOB_1MB,TEST_SCHEMA.NOT_NULL_TBL.DATE_ AS DATE_,TEST_SCHEMA.NOT_NULL_TBL.DECIMAL_5_0 AS DECIMAL_5_0,TEST_SCHEMA.NOT_NULL_TBL.DOUBLE_ AS DOUBLE_,TEST_SCHEMA.NOT_NULL_TBL.INTEGER_ AS INTEGER_,TEST_SCHEMA.NOT_NULL_TBL.REAL_ AS REAL_,TEST_SCHEMA.NOT_NULL_TBL.SMALLINT_ AS SMALLINT_,TEST_SCHEMA.NOT_NULL_TBL.TIME_ AS TIME_,TEST_SCHEMA.NOT_NULL_TBL.TIMESTAMP_ AS TIMESTAMP_,TEST_SCHEMA.NOT_NULL_TBL.VARCHAR_50 AS VARCHAR_50 FROM TEST_SCHEMA.NOT_NULL_TBL;

and here is the original version that I made by hand, which does create the foreign keys properly:

    -- Changeset /tmp/test_schema.xml::1111111111111-1::root (generated)::(Checksum: 2:056bc56736addda50c7121219bf901ec) CREATE TABLE `TEST_SCHEMA`.`NOT_NULL_TBL` ( `INTEGER_PK` INT AUTO_INCREMENT  NOT NULL, `INTEGER_ID` INT NOT NULL, `INTEGER_UNIQ` INT NOT NULL, `BIGINT_` BIGINT NOT NULL, `BLOB_1MB` BLOB NOT NULL, `CHAR_1` CHAR(1) NOT NULL, `CLOB_1MB` TEXT NOT NULL, `DATE_` DATE NOT NULL, `DECIMAL_5_0` DECIMAL(5,0) NOT NULL, `DOUBLE_` DOUBLE NOT NULL, `INTEGER_` INT NOT NULL, `REAL_` REAL NOT NULL, `SMALLINT_` SMALLINT NOT NULL, `TIME_` TIME NOT NULL, `TIMESTAMP_` DATETIME NOT NULL, `VARCHAR_50` VARCHAR(50) NOT NULL, CONSTRAINT `PK_NOT_NULL_TBL` PRIMARY KEY (`INTEGER_PK`), CONSTRAINT `UNIQ_NOT_NULL_TBL` UNIQUE (`INTEGER_UNIQ`) ) ENGINE=INNODB;

    – Changeset /tmp/test_schema.xml::1111111111111-2::root (generated)::(Checksum: 2:7c78a789bd7f3a4da25fc7d0cb14f8a3)
    CREATE TABLE TEST_SCHEMA.NULL_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL,
    INTEGER_ID INT NOT NULL,
    INTEGER_UNIQ INT NOT NULL,
    BIGINT_ BIGINT,
    BLOB_1MB BLOB,
    CHAR_1 CHAR(1),
    CLOB_1MB TEXT,
    DATE_ DATE,
    DECIMAL_5_0 DECIMAL(5,0),
    DOUBLE_ DOUBLE,
    INTEGER_ INT,
    REAL_ REAL,
    SMALLINT_ SMALLINT,
    TIME_ TIME,
    TIMESTAMP_ DATETIME,
    VARCHAR_50 VARCHAR(50),
    CONSTRAINT PK_NULL_TBL PRIMARY KEY (INTEGER_PK),
    CONSTRAINT UNIQ_NULL_TBL UNIQUE (INTEGER_UNIQ)
    ) ENGINE=INNODB;

    – Changeset /tmp/test_schema.xml::1111111111111-3::root (generated)::(Checksum: 2:e93f42f53eda2dfa1f80084e0744837a)
    CREATE TABLE TEST_SCHEMA.NULL_WITH_DEFAULT_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL,
    INTEGER_ID INT NOT NULL,
    INTEGER_UNIQ INT NOT NULL,
    BIGINT_ BIGINT DEFAULT 1,
    CHAR_1 CHAR(1) DEFAULT ‘a’,
    CLOB_1MB TEXT,
    DATE_ DATE DEFAULT ‘2000-01-01’,
    DECIMAL_5_5 DECIMAL(5,5) DEFAULT 0,
    DOUBLE_ DOUBLE DEFAULT 3.14,
    INTEGER_ INT DEFAULT 1,
    REAL_ REAL DEFAULT 3.14,
    SMALLINT_ SMALLINT DEFAULT 1,
    TIME_ TIME DEFAULT ‘01:23:00’,
    TIMESTAMP_ DATETIME,
    VARCHAR_50 VARCHAR(50) DEFAULT ‘abc123’,
    CONSTRAINT PK_NULL_WITH_DEFAU PRIMARY KEY (INTEGER_PK),
    CONSTRAINT UNIQ_NULL_WITH_DEF UNIQUE (INTEGER_UNIQ)
    ) ENGINE=INNODB;

    – Changeset /tmp/test_schema.xml::1111111111111-4::root (generated)::(Checksum: 2:e93f42f53eda2dfa1f80084e0744837a)
    CREATE TABLE TEST_SCHEMA.SMALL_TBL (INTEGER_PK INT AUTO_INCREMENT  NOT NULL,
    INTEGER_UNIQ INTEGER NOT NULL,
    INTEGER_ INTEGER,
    CONSTRAINT PK_SMALL_TBL PRIMARY KEY (INTEGER_PK),
    CONSTRAINT UNIQ_SMALL_TBL_1 UNIQUE (INTEGER_UNIQ)
    ) ENGINE=INNODB;

    – Changeset /tmp/test_schema.xml::1111111111111-5::root (generated)::(Checksum: 2:e93f42f53eda2dfa1f80084e0744837a)
    CREATE TABLE TEST_SCHEMA.SMALL_TBL_NO_PK (
    INTEGER_ INTEGER NOT NULL
    ) ENGINE=INNODB;

    – Changeset /tmp/test_schema.xml::2222222222222-1::root (generated)::(Checksum: 2:f44ee04280a0c1625a03b0dca3412b82)
    ALTER TABLE TEST_SCHEMA.NULL_TBL
    ADD CONSTRAINT FK_0001 FOREIGN KEY (INTEGER_UNIQ) REFERENCES NOT_NULL_TBL(INTEGER_UNIQ) ON UPDATE NO ACTION ON DELETE NO ACTION;

    – Changeset /tmp/test_schema.xml::2222222222222-2::root (generated)::(Checksum: 2:b57ea876519889039c55be27937abea0)
    ALTER TABLE TEST_SCHEMA.NULL_WITH_DEFAULT_TBL
    ADD CONSTRAINT FK_0002 FOREIGN KEY (INTEGER_UNIQ) REFERENCES NOT_NULL_TBL(INTEGER_UNIQ) ON UPDATE NO ACTION ON DELETE NO ACTION;

    – Changeset /tmp/test_schema.xml::3333333333333-2::root (generated)::(Checksum: 2:98c847005ee3cb97df4794bd5c8e62ad)
    CREATE INDEX TEST_INDEX2 ON TEST_SCHEMA.NOT_NULL_TBL(INTEGER_);

    – Changeset /tmp/test_schema.xml::3333333333333-3::root (generated)::(Checksum: 2:a9d02d3d239ee49d27f58001820e1710)
    CREATE INDEX TEST_INDEX1 ON TEST_SCHEMA.NULL_TBL(INTEGER_);

    – Changeset /tmp/test_schema.xml::3333333333333-4::root (generated)::(Checksum: 2:8dfc4e1891c8ebeb90a8c7eecaf34040)
    CREATE UNIQUE INDEX TEST_INDEX3 ON TEST_SCHEMA.NULL_WITH_DEFAULT_TBL(BIGINT_,
    TIME_);

    – Changeset /tmp/test_schema.xml::1272576312598-12::root (generated)::(Checksum: 2:f9055660845838c9f76ec207bb78ef28)
    CREATE VIEW TEST_SCHEMA.NULL_AND_NOT_NULL AS SELECT INTEGER_PK,
    BIGINT_,
    BLOB_1MB,
    CHAR_1,
    CLOB_1MB,
    DATE_,
    DECIMAL_5_0,
    DOUBLE_,
    INTEGER_,
    REAL_,
    SMALLINT_,
    TIME_,
    TIMESTAMP_,
    VARCHAR_50 FROM TEST_SCHEMA.NULL_TBL UNION ALL SELECT INTEGER_PK,
    BIGINT_,
    BLOB_1MB,
    CHAR_1,
    CLOB_1MB,
    DATE_,
    DECIMAL_5_0,
    DOUBLE_,
    INTEGER_,
    REAL_,
    SMALLINT_,
    TIME_,
    TIMESTAMP_,
    VARCHAR_50 FROM TEST_SCHEMA.NOT_NULL_TBL;

Can anyone see why the first one doesn’t create the FK_0001 & FK_0002 foreign keys, but the 2nd one does.
I used the following query in MySQL to verify whether the constraints were created properly:

    select * from information_schema.TABLE_CONSTRAINTS tc where tc.TABLE_SCHEMA = 'TEST_SCHEMA';
2

Wait, I think I found it.
My version has ENGINE=INNODB at the end of each CREATE TABLE statement, but the Liquibase version doesn’t.  When I add ENGINE=INNODB to the Liquibase version, it does create the foreign keys.

I’m not sure what ENGINE=INNODB does or why Liquibase doesn’t add it.  Is there a way to make Liquibase add ENGINE=INNODB to all the CREATE TABLE lines for MySQL?

3

Innodb is one of the mysql engines that provides transactional support.  The default does not which is why the foreign keys did not get created.  You can also set your default table type to innodb in the my.cnf file so that all tables you create in that mysql instance will be innodb unless you specify otherwise.  In case you create tables some other way this insures you always get transactional tables by default.  The other default engine is useful when you don’t need transactional integrity like reporting etc.

4

Thanks.
Setting the default in my.cnf works.
I wish MySQL would give me an error if I try to add a foreign key with the default MyISAM engine, so I’d know if my constraints got created properly or not.  :(  Otherwise people that use my program would have to remember to always change their my.cnf file manually (since they won’t get any SQL errors if they use foreign keys, but they just won’t get added).

I also tried the tag from here:  http://www.liquibase.org/manual/modify_sql but it throws this exception:

    java.sql.SQLException: Can't create table './TEST_SCHEMA/#sql-61b6_28.frm' (errno: 150)
5

Is there a way to get Liquibase to print out the exact SQL commands it’s sending to MySQL when it’s trying to apply a changelog to the database?
I’d like to see how the SQL gets affected by adding this to the XML:

                   
6

You can run updateSql instead of update and it will output an update script without modifying the database.

You may get info if you set logLevel=TRACE as well

Nathan

7

Do you think it would be appropriate for a future version of Liquibase to automatically add:

                   
to the tags of tables that use or are referenced by foreign keys in the changelog produced after running a Diff? If it doesn't do that, and MySQL isn't set to use InnoDB by default, then the Foreign Keys will not be added.
8

It’s not something I would want to add by default, but it would make a great extension (http://liquibase.org/extensions).  Especially as mysql works to support more engines than just innodb and myisam  I wouldn’t want to force innodb if they explicitly have a different default set.

Nathan