0

I want to create table with foreign keys.

This is scheme of this table:

CREATE TABLE `SupplierOrderGoods` (
    `shopOrder_id` INT(11) NOT NULL,
    `supplierGood_id` INT(11) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`shopOrder_id`, `supplierGood_id`),
    CONSTRAINT `FK_SupplierOrderGoods_ShopOrders` FOREIGN KEY (`shopOrder_id`) REFERENCES `shoporders` (`id`),
    CONSTRAINT `FK_SupplierOrderGoods_SupplierGoods` FOREIGN KEY (`supplierGood_id`) REFERENCES `suppliergoods` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

It has two links: to table SupplierGoods — FK_SupplierOrderGoods_SupplierGoods and to table ShopOrders — FK_SupplierOrderGoods_ShopOrders. When I execute this code either in HeidiSQL or in phpMyAdmin odd index FK_SupplierOrderGoods_SupplierGoods is being created.

I don't specify it in my scheme, but it is being created. I realized it when I had seen this in HeidiSQL:

image

Also, there are possibility to see create-code of the table in heidi:

CREATE TABLE `SupplierOrderGoods` (
    `shopOrder_id` INT(11) NOT NULL,
    `supplierGood_id` INT(11) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`shopOrder_id`, `supplierGood_id`),
    ------> INDEX `FK_SupplierOrderGoods_SupplierGoods` (`supplierGood_id`),
    CONSTRAINT `FK_SupplierOrderGoods_ShopOrders` FOREIGN KEY (`shopOrder_id`) REFERENCES `shoporders` (`id`),
    CONSTRAINT `FK_SupplierOrderGoods_SupplierGoods` FOREIGN KEY (`supplierGood_id`) REFERENCES `suppliergoods` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

I didn't specify INDEX, but it is here. I am scared... Why ? :'(

Why this index appears? I think my heidi is broken, so tried in phpmyadmin, but got same result.

I thinked if it can be correct... but why only 1 index for only 1 field? Why not two indexes if it is correct?

Update

Wow! I just realized, that all tables with foreign key has index! I googled it and found this. So it it normal, that index is being created with fk, right?

Then, why only 1 index is being created, but not two???

Community
  • 1
  • 1
Sharikov Vladislav
  • 7,049
  • 9
  • 50
  • 87
  • Probably because your primary key already begins with the other key. – shmosel Jun 16 '14 at 00:09
  • Did you answered the question why only one created instead of two? – Sharikov Vladislav Jun 16 '14 at 00:12
  • 1
    When you have a multi-field index, it can only be used in the order of the keys. So if you have a key `(ColumnA, ColumnB)`, it can be used to lookup rows by `ColumnA, ColumnB`, or just by `ColumnA`, but not just `ColumnB`. I'm guessing that's why MySQL had to make another index. – shmosel Jun 16 '14 at 00:16
  • OK. I thought, that I should create another index for another field and did it. I also set shopOrderId as index too. Am I right? – Sharikov Vladislav Jun 16 '14 at 00:19
  • I thought it was being created automatically. – shmosel Jun 16 '14 at 00:21
  • Yes yes. In my question, I said it. Field supplierGoodId is set as index automatically. I added another index manually f(because I thought it is strange that only one field his has to been index) an asking if it is okay. – Sharikov Vladislav Jun 16 '14 at 00:22
  • Don't see why it would be a problem. – shmosel Jun 16 '14 at 00:23

0 Answers0