CODEWITHSUNDEEP
mysqlforeign-keysexplain
lubart
lubart

Reputation: 1838

mysql foreign key does not work

I have two tables:

CREATE TABLE IF NOT EXISTS treaties(
id INT NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;

and

CREATE TABLE IF NOT EXISTS items(
id INT NOT NULL AUTO_INCREMENT,
treaty INT NOT NULL,
item varchar(20),
PRIMARY KEY(id),
FOREIGN KEY (treaty) REFERENCES treaties(id)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT
)ENGINE=InnoDB;

After that I inserted few lines in each of tables but values treaties.id and items.treaty were the same. When I run

EXPLAIN SELECT * 
FROM `items` 
JOIN `treaties` ON `items`.`treaty` = `treaties`.`id` 
WHERE 1

I obtained:

id | select_type | table   | type | possible_keys | key | key_len | ref | rows | Extra
1  | SIMPLE      | treaties| ALL  | PRIMARY       | NULL|   NULL  | NULL|   3  |
1  | SIMPLE      | items   | ALL  | treaty        | NULL|   NULL  | NULL|   4  | Using where; Using join buffer

I thought if I have foreign key between items.treaty and treaties.id this key must used and type must not be ALL. What is wrong?

Please, help me!

Thank you!

Upvotes: 1

Views: 79

Answers (1)

eggyal
eggyal

Reputation: 125855

As explained in the manual:

The output from EXPLAIN shows ALL in the type column when MySQL uses a table scan to resolve a query. This usually happens under the following conditions:

[...]

  • The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length. Don't worry in this case.

Upvotes: 1

Related Questions