CODEWITHSUNDEEP
mysqlduplicatesforeign-keyscomposite-primary-key
Dennis
Dennis

Reputation: 8111

Does using Identifying Many-To-One Relationship means having to have duplicate PRIMARY and FOREIGN keys on same table?

Original Title: When PRIMARY KEY and foreign key INDEX are set on the same column names, does that mean that same index is duplicated?

Example:

I've generated a table using MySQL Workbench, and using Identifying relationship to other tables. That means that PRIMARY KEY contains FOREIGN KEY columns in my case.

CREATE TABLE IF NOT EXISTS `price_history` (
`amount` DECIMAL NULL,
  `date_start` DATE NULL,
  `date_end` DATE NULL,
  `product_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `priceitem_id` INT NOT NULL,
  PRIMARY KEY (`product_id`, `category_id`, `priceitem_id`),
  INDEX `fk_idx` (`product_id` ASC, `category_id` ASC, `priceitem_id` ASC),
ENGINE = InnoDB;

I note that PRIMARY KEY and INDEX define the same columns. Does that mean I will have two separate "physical" indices on the table? Won't that be wasteful?

I am wondering if it a necessary evil of identifying relationships.

Upvotes: 0

Views: 147

Answers (1)

Drew
Drew

Reputation: 24960

I don't see this happening if I understand your question. In the below, price_history does not have a db engine auto-created index that duplicates.

create table A
(   `product_id` INT NOT NULL,
    `category_id` INT NOT NULL,
    `priceitem_id` INT NOT NULL,
    PRIMARY KEY (`product_id`, `category_id`, `priceitem_id`)

)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `price_history` (
    `amount` DECIMAL NULL,
    `date_start` DATE NULL,
    `date_end` DATE NULL,
    `product_id` INT NOT NULL,
    `category_id` INT NOT NULL,
    `priceitem_id` INT NOT NULL,
    PRIMARY KEY (`product_id`, `category_id`, `priceitem_id`),
    FOREIGN KEY `f` (`product_id`, `category_id`, `priceitem_id`) references A(`product_id`, `category_id`, `priceitem_id`)
)ENGINE = InnoDB;

show create table price_history;
CREATE TABLE `price_history` (
   `amount` decimal(10,0) DEFAULT NULL,
   `date_start` date DEFAULT NULL,
   `date_end` date DEFAULT NULL,
   `product_id` int(11) NOT NULL,
   `category_id` int(11) NOT NULL,
   `priceitem_id` int(11) NOT NULL,
   PRIMARY KEY (`product_id`,`category_id`,`priceitem_id`),
   CONSTRAINT `price_history_ibfk_1` FOREIGN KEY (`product_id`, `category_id`, `priceitem_id`) 
      REFERENCES `a` (`product_id`, `category_id`, `priceitem_id`)
 ) ENGINE=InnoDB;

 CREATE TABLE IF NOT EXISTS `price_history` (
    `amount` DECIMAL NULL,
    `date_start` DATE NULL,
    `date_end` DATE NULL,
    `product_id` INT NOT NULL,
    `category_id` INT NOT NULL,
    `priceitem_id` INT NOT NULL,
    PRIMARY KEY (`product_id`, `category_id`, `priceitem_id`),
    FOREIGN KEY `f` (`product_id`, `category_id`, `priceitem_id`) references A(`product_id`, `category_id`, `priceitem_id`)
)ENGINE = InnoDB;

show create table A;
CREATE TABLE `a` (
   `product_id` int(11) NOT NULL,
   `category_id` int(11) NOT NULL,
   `priceitem_id` int(11) NOT NULL,
   PRIMARY KEY (`product_id`,`category_id`,`priceitem_id`)
 ) ENGINE=InnoDB;

 CREATE TABLE IF NOT EXISTS `price_historyBBB` (
    id int auto_increment primary key,
    `amount` DECIMAL NULL,
    `date_start` DATE NULL,
    `date_end` DATE NULL,
    `product_id` INT NOT NULL,
    `category_id` INT NOT NULL,
    `priceitem_id` INT NOT NULL,
    FOREIGN KEY `g` (`product_id`, `category_id`, `priceitem_id`) references A(`product_id`, `category_id`, `priceitem_id`)
)ENGINE = InnoDB;

show create table price_historyBBB;
CREATE TABLE `price_historybbb` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `amount` decimal(10,0) DEFAULT NULL,
   `date_start` date DEFAULT NULL,
   `date_end` date DEFAULT NULL,
   `product_id` int(11) NOT NULL,
   `category_id` int(11) NOT NULL,
   `priceitem_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `g` (`product_id`,`category_id`,`priceitem_id`),
   CONSTRAINT `price_historybbb_ibfk_1` FOREIGN KEY (`product_id`, `category_id`, `priceitem_id`) 
      REFERENCES `a` (`product_id`, `category_id`, `priceitem_id`)
 ) ENGINE=InnoDB;

show indexes from price_history;
show indexes from price_historyBBB;

So, if there exists an adequate key (say, composite) left-most chunk adequate enough for re-use, then the db engine will not auto-create a Helper index.

For instance, if you had a key (PK or otherwise) that was a composite of (col1,col2,col3,col5) and your FK called for the use of (col1,col2), then a new index is not auto-gen'd.

If the need for the FK was for (colX,col1,col2) then the above (col1,col2,col3,col5) is not useful (left-most priority) and the db engine will need to create an FK helper index.

Upvotes: 1

Related Questions