131 lines
3.3 KiB
SQL
131 lines
3.3 KiB
SQL
-- Crear tabla en caso de necesitar recuperar
|
|
|
|
CREATE TABLE vn.expence__ LIKE vn.expence;
|
|
INSERT INTO vn.expence__ SELECT * FROM vn.expence;
|
|
|
|
-- Eliminar las Fk en expence
|
|
|
|
ALTER TABLE `vn`.`invoiceOutExpence`
|
|
DROP FOREIGN KEY `invoiceOutExpence_expenceFk`;
|
|
ALTER TABLE `vn`.`invoiceOutExpence`
|
|
DROP INDEX `invoiceOutExpence_FK_2_idx` ;
|
|
|
|
ALTER TABLE `vn`.`item`
|
|
DROP FOREIGN KEY `item_expenceFk`;
|
|
ALTER TABLE `vn`.`item`
|
|
DROP INDEX `item_expenceFk` ;
|
|
|
|
ALTER TABLE `vn`.`invoiceInTax`
|
|
DROP FOREIGN KEY `recibida_iva_gastos_id`;
|
|
ALTER TABLE `vn`.`invoiceInTax`
|
|
DROP INDEX `recibida_iva_gastos_id` ;
|
|
|
|
ALTER TABLE `vn`.`invoiceInSage`
|
|
DROP FOREIGN KEY `invoiceInSage_expenceFk`;
|
|
ALTER TABLE `vn`.`invoiceInSage`
|
|
DROP INDEX `invoiceInSage_expenceFk` ;
|
|
|
|
ALTER TABLE `vn`.`invoiceIn`
|
|
DROP FOREIGN KEY `invoiceIn_expenceFkDeductible`;
|
|
ALTER TABLE `vn`.`invoiceIn`
|
|
DROP INDEX `invoiceIn_expenceFkDeductible` ;
|
|
|
|
ALTER TABLE `vn`.`ticketServiceType`
|
|
DROP FOREIGN KEY `ticketServiceType_expenceFk`;
|
|
ALTER TABLE `vn`.`ticketServiceType`
|
|
DROP INDEX `ticketServiceType_expenceFk` ;
|
|
|
|
-- Actualizar tabla expence
|
|
|
|
ALTER TABLE `vn`.`expence`
|
|
DROP INDEX `iva_tipo_id`;
|
|
|
|
ALTER TABLE `vn`.`expence`
|
|
DROP COLUMN `isForSale`,
|
|
DROP COLUMN `isConbase`;
|
|
|
|
CREATE TEMPORARY TABLE tmp.expence
|
|
SELECT *
|
|
FROM vn.expence
|
|
GROUP BY id;
|
|
|
|
TRUNCATE vn.expence;
|
|
|
|
ALTER TABLE `vn`.`expence`
|
|
CHANGE COLUMN `taxTypeFk` `taxTypeFk` TINYINT(4) NULL ,
|
|
DROP PRIMARY KEY,
|
|
ADD PRIMARY KEY (`id`);
|
|
|
|
ALTER TABLE `vn`.`expence`
|
|
DROP COLUMN `taxTypeFk`;
|
|
|
|
|
|
INSERT INTO vn.expence (id, name ,isWithheld)
|
|
SELECT id, name ,isWithheld
|
|
FROM tmp.expence;
|
|
|
|
|
|
-- Volver a poner las Fk en expence
|
|
|
|
ALTER TABLE `vn`.`invoiceOutExpence`
|
|
ADD INDEX `invoiceOutExpence_expenceFk_idx` (`expenceFk` ASC) ;
|
|
|
|
ALTER TABLE `vn`.`invoiceOutExpence`
|
|
ADD CONSTRAINT `invoiceOutExpence_expenceFk`
|
|
FOREIGN KEY (`expenceFk`)
|
|
REFERENCES `vn`.`expence` (`id`)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `vn`.`item`
|
|
ADD INDEX `item_expenceFk_idx` (`expenceFk` ASC) ;
|
|
|
|
ALTER TABLE `vn`.`item`
|
|
ADD CONSTRAINT `item_expenceFk`
|
|
FOREIGN KEY (`expenceFk`)
|
|
REFERENCES `vn`.`expence` (`id`)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE;
|
|
|
|
|
|
ALTER TABLE `vn`.`invoiceInTax`
|
|
ADD INDEX `invoiceInTax_idx` (`expenceFk` ASC) ;
|
|
|
|
ALTER TABLE `vn`.`invoiceInTax`
|
|
ADD CONSTRAINT `invoiceInTax_expenceFk`
|
|
FOREIGN KEY (`expenceFk`)
|
|
REFERENCES `vn`.`expence` (`id`)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE;
|
|
|
|
|
|
ALTER TABLE `vn`.`invoiceInSage`
|
|
ADD INDEX `invoiceInSage_idx` (`expenceFk` ASC) ;
|
|
|
|
ALTER TABLE `vn`.`invoiceInSage`
|
|
ADD CONSTRAINT `invoiceInSage_expenceFk`
|
|
FOREIGN KEY (`expenceFk`)
|
|
REFERENCES `vn`.`expence` (`id`)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `vn`.`invoiceIn`
|
|
ADD INDEX `invoiceIn_expenceFkDeductible_idx` (`expenceFkDeductible` ASC) ;
|
|
|
|
ALTER TABLE `vn`.`invoiceIn`
|
|
ADD CONSTRAINT `invoiceIn_expenceFkDeductible`
|
|
FOREIGN KEY (`expenceFkDeductible`)
|
|
REFERENCES `vn`.`expence` (`id`)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `vn`.`ticketServiceType`
|
|
ADD INDEX `ticketServiceType_expenceFk_idx` (`expenceFk` ASC) ;
|
|
|
|
ALTER TABLE `vn`.`ticketServiceType`
|
|
ADD CONSTRAINT `ticketServiceType_expenceFk`
|
|
FOREIGN KEY (`expenceFk`)
|
|
REFERENCES `vn`.`expence` (`id`)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE;
|
|
|