Nie będę się rozpisywać na temat tego, czym są klucze obce. Jeśli ktoś nie miał okazji się z nimi zetknąć, zapraszam do Google. Chciałbym natomiast napisać o tym, jakie wymogi muszą być spełnione, aby klucz obcy mógł zostać utworzony. Nie do końca jest to jasne, a świadczy o tym choćby fakt pojawienia się na jednym z czytanych przeze mnie forów opisu problemu z tworzeniem takiego klucza.

Załóżmy, że mamy dwie tabele, które tworzymy w następujący sposób:

CREATE TABLE tab1 (tab1_id SERIAL, tab2_id INT, CONSTRAINT pk PRIMARY KEY (tab1_id)) ENGINE=InnoDB;
CREATE TABLE tab2 (tab2_id SERIAL, kol INT, CONSTRAINT pk2 PRIMARY KEY (tab2_id)) ENGINE=InnoDB;

Chcemy je połączyć kluczem obcym na kolumnach tab2_id. Próbujemy taki klucz obcy utworzyć:

mysql> ALTER TABLE tab1 ADD CONSTRAINT tab1_tab2_fk FOREIGN KEY (tab2_id) REFERENCES tab2(tab2_id);
ERROR 1005 (HY000): Nie można stworzyć tabeli 'test.#sql-6ed0_b0a9' (Kod błędu: 150)

Co jest nie tak?

Najprościej to sprawdzić przy pomocy polecenia:

mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
101103 19:57:27 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
----------
BACKGROUND THREAD
----------
srv_master_thread loops: 106 1_second, 106 sleeps, 9 10_second, 21 background, 21 flush
srv_master_thread log flush and writes: 110
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 18, signal count 17
Mutex spin waits 35, rounds 1050, OS waits 2
RW-shared spins 16, OS waits 16; RW-excl spins 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
101103 19:56:34 Error in foreign key constraint of table test/#sql-6ed0_b0a9:
FOREIGN KEY (tab2_id) REFERENCES tab2(tab2_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Wynik jest oczywiście dłuższy, ale nas interesuje sekcja „LATEST FOREIGN KEY ERROR”. Informacja o błędzie jest co prawda dość ogólna, ale pewne sugestie się pojawiają. Indeksy są założone prawidłowo, więc coś nie tak musi być z kolumnami. Sprawdzamy dokładnie jak wyglądają obie tabele:

mysql> SHOW CREATE TABLE tab1\G
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`tab1_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tab2_id` int(11) DEFAULT NULL,
PRIMARY KEY (`tab1_id`),
UNIQUE KEY `tab1_id` (`tab1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE tab2\G
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`tab2_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kol` int(11) DEFAULT NULL,
PRIMARY KEY (`tab2_id`),
UNIQUE KEY `tab2_id` (`tab2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2
1 row in set (0,00 sec)

Widać różnicę – typ SERIAL w praktyce definiowany jest jako BIGINT(20) UNSIGNED, podczas gdy w tabeli `tab1` kolumna `tab2_id` zdefiniowana jest jako INT(11). Zgodnie z dokumentacją, w przypadku typów numerycznych całych (INTEGER i pochodne), musi zgadzać się zarówno wielkość kolumny jak i dopuszczalne znaki (UNSIGNED lub także ujemne). W przypadku kolumn tekstowych identyczne musi być kodowanie znaków i mechanizm porównań (collation).

Poprawmy więc kolumnę `tab1`.`tab2_id` i dodajmy indeks:

mysql> ALTER TABLE tab1 MODIFY tab2_id BIGINT(20) UNSIGNED NOT NULL;
Query OK, 0 rows affected (0,03 sec)
Rekordów: 0  Duplikatów: 0  Ostrzeżeń: 0

mysql> ALTER TABLE tab1 ADD CONSTRAINT tab1_tab2_fk FOREIGN KEY (tab2_id) REFERENCES tab2(tab2_id);
Query OK, 0 rows affected (0,06 sec)
Rekordów: 0  Duplikatów: 0  Ostrzeżeń: 0

Jak widać, klucz obcy się ładnie założył. Niby sprawa prosta, a okazuje się, że może sprawić problem, tym bardziej że sam komunikat o błędzie nie wyjaśnia za wiele. Kod błędu mówi już więcej, ale kto by sprawdzał takie rzeczy w Google…