4

I have a general doubt in MySql foreign key constraints and Magento.

As per MySql, a table should have only one primary key for a table and a Primary key should contain unique values.

I checked in "cms_block_store" and "cms_page_store" Magento tables. In that, both table block_id, page_id, and store_id columns are declared as foreign keys and at the same time mentioned as primary keys.

Here two primary keys declared in a table and A foreign key cannot contain unique values but at the same time A primary key should contain unique values, but here the fields are working with the duplicate entries even the fields are mentioned as Primary keys.

Please check the attached screenshots:

"cms_block_store" table structure:

enter image description here

"cms_page_store" table structure:

enter image description here

Can someone help me to understand how working in Magento against MySql?

2

We can see the table create-query by running the query in Mysql console show create table cms_page_store.

CREATE TABLE `cms_page_store` (
  `page_id` smallint(6) NOT NULL COMMENT 'Entity ID',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
  PRIMARY KEY (`page_id`,`store_id`),
  KEY `CMS_PAGE_STORE_STORE_ID` (`store_id`),
  CONSTRAINT `CMS_PAGE_STORE_PAGE_ID_CMS_PAGE_PAGE_ID` FOREIGN KEY (`page_id`) REFERENCES `cms_page` (`page_id`) ON DELETE CASCADE,
  CONSTRAINT `CMS_PAGE_STORE_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='CMS Page To Store Linkage Table'

We can see PRIMARY KEY (page_id,store_id) in the above results. Also, we can see the same in desc cms_page_store.

enter image description here

Can I have multiple primary keys in a single table?

A Table can have a Composite Primary Key which is a primary key made from two or more columns. For example:

CREATE TABLE userdata (
  userid INT,
  userdataid INT,
  info char(200),
  primary key (userid, userdataid)
);

Easy for your reference:

https://stackoverflow.com/a/217952/5528059 https://weblogs.sqlteam.com/jeffs/2007/08/23/composite_primary_keys/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.