0

I have products related to a custom pages, I want to display these products on the page in a specific order for this I have created a new table;

CREATE TABLE `am_landing_page_products` 
(
  `rel_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relation ID',
  `page_id` smallint(6) NOT NULL COMMENT 'Xlanding ID',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `position` int(11) NOT NULL DEFAULT '0' COMMENT 'Position',

  PRIMARY KEY (`rel_id`),

  UNIQUE KEY `UNQ_AM_LANDING_PAGE_PRODUCT_LANDING_ID_PRODUCT_ID` (`page_id`,`product_id`),

  UNIQUE KEY `uniq_position` (`page_id`,`position`),

  KEY `IDX_AM_LANDING_PAGE_PRODUCT_PRODUCT_ID` (`product_id`),

  CONSTRAINT `FK_AM_LANDING_PAGE_PRD_PRD_ID_CAT_PRD_ENTT_ENTT_ID` 
    FOREIGN KEY (`product_id`) 
    REFERENCES `catalog_product_entity` (`entity_id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `fk_landingpage` 
    FOREIGN KEY (`page_id`) 
    REFERENCES `am_landing_page` (`page_id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Here product_id and page_id columns are compositely unique, now I want that for each unique combination of page_id and product_id, the position should be unique. Can someone help me with this.

Edit

I added position to unique key also, this will make the things, but is there any way where I enter page_id and product_id and the position is populated with incremented value for that combination?

  • @McNets it is mysql – Haris Jan 15 '18 at 14:09
  • You should emulate a sequence function. convert-in.com/mysql-sequence.htm – McNets Jan 15 '18 at 14:13
  • @McNets actually I changed the table structure, now I have two composite unique keys, one, page_id,product_id and second one is page_id,position. just copy-pasted creation code here – Haris Jan 15 '18 at 14:20
  • Can you clarify what you mean by "product_id and page_id columns are compositely unique, now I want that for each unique combination of page_id and product_id, the position should be unique". Can you give an example of a couple of 3-tuples that would violate this property? – Lennart Jan 16 '18 at 8:02
  • @Lennart as you can see here page_id and product_id ` UNIQUE KEY UNQ_AM_LANDING_PAGE_PRODUCT_LANDING_ID_PRODUCT_ID (page_id,product_id),`. but as I mentioned in the edit, that now I have added position to unique key also (page_id,position), the unique combination requirement is done, now I want to know if there is a way where I insert page_id and product_id and mysql insert value for position which is unique for each page_id – Haris Jan 16 '18 at 11:17

Your Answer

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

Browse other questions tagged or ask your own question.