34 lines
1.4 KiB
MySQL
34 lines
1.4 KiB
MySQL
|
-- Place your SQL code here
|
||
|
|
USE vn;
|
||
|
|
ALTER TABLE `vn`.`saleGroup`
|
||
|
|
ADD COLUMN IF NOT EXISTS `ticketFk` int(11) NULL,
|
||
|
|
ADD COLUMN IF NOT EXISTS `editorFk` int(10) unsigned DEFAULT NULL;
|
||
|
|
|
||
|
|
ALTER TABLE vn.saleGroup ADD CONSTRAINT saleGroup_FK
|
||
|
|
FOREIGN KEY (ticketFk) REFERENCES vn.ticket(id) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE vn.saleGroup
|
||
|
|
MODIFY COLUMN userFk int(10) unsigned DEFAULT NULL NULL;
|
||
|
|
|
||
|
|
ALTER TABLE vn.saleGroup ADD CONSTRAINT saleGroup_userFK
|
||
|
|
FOREIGN KEY (userFk) REFERENCES account.`user`(id) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE vn.saleGroup ADD CONSTRAINT saleGroup_parkingFK
|
||
|
|
FOREIGN KEY (parkingFk) REFERENCES vn.`parking`(id) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
ALTER TABLE vn.saleGroup ADD CONSTRAINT saleGroup_sectorFK
|
||
|
|
FOREIGN KEY (sectorFk) REFERENCES vn.`sector`(id) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
UPDATE `vn`.`saleGroup` sg
|
||
|
|
JOIN (
|
||
|
|
SELECT sgd.saleGroupFk, s.ticketFk
|
||
|
|
FROM saleGroupDetail sgd
|
||
|
|
JOIN sale s ON s.id = sgd.saleFk
|
||
|
|
GROUP BY sgd.saleGroupFk
|
||
|
|
) sub ON sg.id = sub.saleGroupFk
|
||
|
|
SET sg.ticketFk = sub.ticketFk;
|
||
|
|
|
||
|
|
ALTER TABLE vn.ticketLog MODIFY COLUMN changedModel enum('Ticket','Sale','TicketWeekly','TicketTracking','TicketService','TicketRequest',
|
||
|
|
'TicketRefund','TicketPackaging','TicketObservation','TicketDms','Expedition','Sms','SaleGroup')
|
||
|
|
CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT 'Ticket' NOT NULL;
|