40 lines
1.1 KiB
MySQL
40 lines
1.1 KiB
MySQL
|
|
||
|
|
DROP VIEW IF EXISTS vn.comparative;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS vn.comparative (
|
||
|
|
id int unsigned auto_increment NULL,
|
||
|
|
timePeriod int NULL,
|
||
|
|
itemFk int(11) NULL,
|
||
|
|
warehouseFk smallint(6) unsigned NULL,
|
||
|
|
quantity int NULL,
|
||
|
|
price int NULL,
|
||
|
|
countryFk mediumint(8) unsigned NULL,
|
||
|
|
CONSTRAINT comparative_PK PRIMARY KEY (id),
|
||
|
|
CONSTRAINT comparative_UN UNIQUE KEY (timePeriod,itemFk,warehouseFk),
|
||
|
|
CONSTRAINT comparativeItem_FK FOREIGN KEY (itemFk) REFERENCES vn.item(id),
|
||
|
|
CONSTRAINT comparativeWarehouse_FK FOREIGN KEY (warehouseFk) REFERENCES vn.warehouse(id),
|
||
|
|
CONSTRAINT comparativeCountry_FK FOREIGN KEY (countryFk) REFERENCES vn.country(id)
|
||
|
|
)
|
||
|
|
ENGINE=InnoDB
|
||
|
|
DEFAULT CHARSET=utf8mb3
|
||
|
|
COLLATE=utf8mb3_unicode_ci;
|
||
|
|
|
||
|
|
INSERT INTO vn.comparative(
|
||
|
|
timePeriod,
|
||
|
|
itemFk,
|
||
|
|
warehouseFk,
|
||
|
|
quantity,
|
||
|
|
price
|
||
|
|
)
|
||
|
|
SELECT c.Periodo,
|
||
|
|
c.Id_Article,
|
||
|
|
c.warehouse_id,
|
||
|
|
c.Cantidad,
|
||
|
|
c.price
|
||
|
|
FROM vn2008.Comparativa c
|
||
|
|
JOIN vn.item i ON i.id = c.Id_Article
|
||
|
|
JOIN vn.warehouse w ON w.id = c.warehouse_id;
|
||
|
|
|
||
|
|
RENAME TABLE vn2008.Comparativa TO vn2008.Comparativa__;
|
||
|
|
|
||
|
|
ALTER TABLE vn2008.Comparativa__ COMMENT= '20/11/2023';
|