item replacement and relationships db update

1 files changed, 11 insertions, 0 deletions

diff --git a/backend/database/dev/update_002_add_asset_relationships.sql b/backend/database/dev/update_002_add_asset_relationships.sql
new file mode 100644
index 0000000..0d74b89
--- /dev/null
+++ b/backend/database/dev/update_002_add_asset_relationships.sql
@@ -0,0 +1,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;