backend/database/dev/backup/update_002_add_asset_relationships.sql


1
2
3
4
5
6
7
8
9
10
11

ALTER TABLE `assets` 
ADD COLUMN `belongs_to_item` INT(11) DEFAULT NULL COMMENT 'References asset_numeric_id of parent asset' AFTER `asset_numeric_id`,
ADD COLUMN `previously_was` INT(11) DEFAULT NULL COMMENT 'References asset_numeric_id of the asset this replaced' AFTER `belongs_to_item`;

ALTER TABLE `assets`
ADD KEY `idx_belongs_to` (`belongs_to_item`),
ADD KEY `idx_previously_was` (`previously_was`);

ALTER TABLE `assets`
ADD CONSTRAINT `fk_assets_belongs_to` FOREIGN KEY (`belongs_to_item`) REFERENCES `assets` (`asset_numeric_id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_assets_previously_was` FOREIGN KEY (`previously_was`) REFERENCES `assets` (`asset_numeric_id`) ON DELETE SET NULL;