Magento Forum

Addin a column to the table (Referential Integrity)
 
vishal_2811
Jr. Member
 
Avatar
Total Posts:  20
Joined:  2011-04-17
 

Hi All,
I created a table “albums” (with album_id auto_increment and primary field) and wanted to use this id as a foreign key into the “wishlist_item” table so the table structure (columns) now should be like this
wishlist_item_id wishlist_id album_id product_id store_id added_at description qty

Steps are as follows:-
======================
1.  Go to app/code/core/Mage/Wishlist/sql/wishlist_setup and get the highest version of the upgraded filename (in may case it is mysql4-upgrade-0.7.8-0.7.9.php i.e. upgraded from 0.7.8 to the new version 0.7.9)

2.Now take the backup of this file and creat the new one with this name itself.

3.Put the following code into this file MIND THAT the datatype and the length of the column must be exactly the same (in my case it is INT(10) UNSIGNED)

$installer $this;
/* @var $installer Mage_Customer_Model_Entity_Setup */
$installer->startSetup();
$installer->run("
ALTER TABLE {
$this->getTable('wishlist_item')}
    ADD COLUMN `album_id` INT(10) UNSIGNED AFTER `product_id`,
    ADD INDEX IDX_ALBUM_ID(`album_id`),
    ADD CONSTRAINT `FK_ALBUM_ID` FOREIGN KEY `FK_ALBUM_ID` (`album_id`)
        REFERENCES {
$this->getTable('albums')} (`album_id`) ON DELETE CASCADE;
"
);
$installer->endSetup();

4.Run the following sql to your concerned DB

UPDATE `myDB`.`core_resourceSET `version` = '0.7.8',
`
data_version` = '0.7.8' WHERE `core_resource`.`code` = 'wishlist_setup';

It MUST BE NOTED here that I have updated the DB ONE VERSION BACK to the concerned record as you might be knowing in advance that WHY I did so? If not, let me tell you, the reason is that if INSTALLER runs only for the latest version (0.7.9 in our case) not for already existing versions into the DB (So we are cheating the DB that 0.7.8 is the existing version and we are updating it with the 0.7.9 hehehehe)

That’s all.

I tried to do this using the overriding (the files) feature to my local namespace BUT IT DIDN’T RUN :(
Please tell me if anyone used overriding approch for this and whether is it possible or not with overriding?

Please NOTE:- I have taken the backup of the file mysql4-upgrade-0.7.8-0.7.9.php instead creating the new one with the name mysql4-upgrade-0.7.9-0.7.10.php because keeping in mind that if in future magento provides an update for this version (basically the magento version and for the wishlist_item table) and for this table and lets say if it uses the filename for the next version as mysql4-upgrade-0.7.9-0.7.10.php (which I could have been used as stated earlier) that WONT be applicable at all.

If I’ll be using the file with the name mysql4-upgrade-0.7.9-0.7.10.php, ONLY step 3, mentioned above, was required in that case.

Thanks n Enjoy.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top