Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql foreign key by non unique key -- how is that possible?

I was migrating mysql database to postgres and stumbled across the following block in DDL (Note: This is what I got from mysqldump):

CREATE TABLE `catalog_property_value` (
  `id` int(10) unsigned NOT NULL,
  `property_id` int(10) unsigned NOT NULL,
  `sort` int(10) unsigned NOT NULL,
  `value_number` decimal(15,5) DEFAULT NULL,
  `value_string` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`sort`),
  KEY `FK_catalog_property_value` (`property_id`),
  KEY `NewIndex1` (`id`),
  CONSTRAINT `FK_catalog_property_value` FOREIGN KEY (`property_id`) REFERENCES `catalog_property` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

CREATE TABLE `catalog_realty_property_value_link` (
  `realty_id` int(10) unsigned NOT NULL,
  `property_id` int(10) unsigned NOT NULL,
  `value_id` int(10) unsigned NOT NULL,
  `dt_is_denormalized` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`realty_id`,`property_id`,`value_id`),
  KEY `FK_catalog_realty_property_value_link_property` (`property_id`),
  KEY `FK_catalog_realty_property_value_link_value` (`value_id`),
  CONSTRAINT `FK_catalog_realty_property_value_link_property` FOREIGN KEY (`property_id`) REFERENCES `catalog_property` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_catalog_realty_property_value_link_realty` FOREIGN KEY (`realty_id`) REFERENCES `catalog_realty` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_catalog_realty_property_value_link_value` FOREIGN KEY (`value_id`) REFERENCES `catalog_property_value` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now, what I see here is that the only unique key in the first table is combination of (id, sort):

PRIMARY KEY (`id`,`sort`),

however, the second table has a reference to the first on by only id column, which is not unique!

CONSTRAINT `FK_catalog_realty_property_value_link_value` FOREIGN KEY (`value_id`) REFERENCES `catalog_property_value` (`id`) ON DELETE CASCADE

So, what did I get wrong here? How is that possible?

like image 614
Maxim Sloyko Avatar asked Mar 02 '10 09:03

Maxim Sloyko


2 Answers

From the manual:

Deviation from SQL standards: A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL.

So it looks like InnoDB allows non-unique indexes as candidates for foreign key references. Elsewhere the manual states that you can reference a subset of columns in the referenced index as long as the referenced columns are listed first and in the same order as the primary key.

Therefore, this definition is legal in InnoDB, although it's not standard SQL and leaves me, at least, a little confused as to the original designer's intentions.

Manual page here.

like image 136
Larry Lustig Avatar answered Nov 03 '22 11:11

Larry Lustig


This weird behavior of FK's in innoDB is described in the manual.

The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys.

PostgreSQL doesn't accept this construction, the foreign key has to point to a unique key.

like image 35
Frank Heikens Avatar answered Nov 03 '22 13:11

Frank Heikens