Several similar DDL with different result in foreign key

Question

This is a further discussion on this question:

http://stackoverflow.com/questions/2321270/why-is-it-still-possible-to-insert-a-foreign-key-that-doesnt-exist

This works:

CREATE TABLE products (
     id integer unsigned auto_increment primary key
) ENGINE=INNODB;

CREATE TABLE orders (
     id integer PRIMARY KEY auto_increment,
     product_id integer unsigned,
     quantity integer,
     INDEX product_id_idx (product_id),
     FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE=INNODB;

But these 2 not:

A

CREATE TABLE products (
     id integer unsigned auto_increment primary key
) ENGINE=INNODB;

CREATE TABLE orders (
    id integer PRIMARY KEY auto_increment,
    product_id integer unsigned REFERENCES products (id),
    quantity integer,
    INDEX product_id_idx (product_id)
);

B

CREATE TABLE products (
     id integer auto_increment primary key
) ENGINE=INNODB;

CREATE TABLE orders (
     id integer PRIMARY KEY auto_increment,
     product_id integer unsigned,
     quantity integer,
     INDEX product_id_idx (product_id),
     FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE=INNODB;

For B,it's because integer primary key is the same as integer unsigned primary key

Can you explain why A and B are not working?

Answer 1

A does not work because InnoDB does not recognize or support the syntax for "Inline REFERENCES specifications." They are simply ignored.

B does not work because the referencing and referenced columns need to be of the exact same type. "The size and sign of integer types must be the same." MySQL's numeric data types are signed by default, so you would have to explicitly specify the unsigned attribute. You can test that INT AUTO_INCREMENT PRIMARY KEY does not imply unsigned:

CREATE TABLE a (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=INNODB;
INSERT INTO a VALUES(-5);
SELECT * FROM a;

Source and further reading:

• MySQL 5.1 Reference Manual: FOREIGN KEY Constraints
• MySQL 5.1 Reference Manual: Overview of Numeric Types