upgrade2.7-2.8.sql

java开源项目源代码

ALTER TABLE ContractBOM ADD INDEX (WorkCentreAdded);

ALTER TABLE ContractBOM ADD FOREIGN KEY (WorkCentreAdded) REFERENCES WorkCentres (Code);

ALTER TABLE ContractBOM ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);
ALTER TABLE ContractBOM ADD FOREIGN KEY (Component) REFERENCES StockMaster (StockID);


ALTER TABLE ContractReqts ADD FOREIGN KEY (Contract) REFERENCES Contracts (ContractRef);


ALTER TABLE `Contracts` DROP INDEX `DebtorNo` , DROP INDEX `BranchCode`, ADD INDEX `DebtorNo` ( `DebtorNo` , `BranchCode` );

ALTER TABLE Contracts ADD FOREIGN KEY (DebtorNo, BranchCode) REFERENCES CustBranch (DebtorNo, BranchCode);
ALTER TABLE Contracts ADD FOREIGN KEY (CategoryID) REFERENCES StockCategory (CategoryID);

ALTER TABLE Contracts ADD FOREIGN KEY (TypeAbbrev) REFERENCES SalesTypes (TypeAbbrev);


ALTER TABLE CustAllocns ADD FOREIGN KEY (TransID_AllocFrom) REFERENCES DebtorTrans (ID);
ALTER TABLE CustAllocns ADD FOREIGN KEY (TransID_AllocTo) REFERENCES DebtorTrans (ID);

ALTER TABLE CustBranch ADD FOREIGN KEY (DebtorNo) REFERENCES DebtorsMaster (DebtorNo);

ALTER TABLE CustBranch ADD INDEX (Area);
ALTER TABLE CustBranch ADD FOREIGN KEY (Area) REFERENCES Areas (AreaCode);
ALTER TABLE CustBranch ADD FOREIGN KEY (Salesman) REFERENCES Salesman (SalesmanCode);

ALTER TABLE `CustBranch` ADD INDEX ( `DefaultLocation` );
ALTER TABLE `CustBranch` ADD INDEX ( `TaxAuthority` );
ALTER TABLE `CustBranch` ADD INDEX ( `DefaultShipVia` );

ALTER TABLE CustBranch ADD FOREIGN KEY (DefaultLocation) REFERENCES Locations (LocCode);

ALTER TABLE `CustBranch` CHANGE `TaxAuthority` `TaxAuthority` TINYINT DEFAULT '1' NOT NULL;

ALTER TABLE CustBranch ADD FOREIGN KEY (TaxAuthority) REFERENCES TaxAuthorities (TaxID);
ALTER TABLE CustBranch ADD FOREIGN KEY (DefaultShipVia) REFERENCES Shippers (Shipper_ID);

ALTER TABLE DebtorTrans ADD FOREIGN KEY (DebtorNo) REFERENCES CustBranch (DebtorNo);
ALTER TABLE DebtorTrans ADD FOREIGN KEY (Type) REFERENCES SysTypes (TypeID);
ALTER TABLE DebtorTrans ADD FOREIGN KEY (Prd) REFERENCES Periods (PeriodNo);

ALTER TABLE DebtorsMaster ADD FOREIGN KEY (HoldReason) REFERENCES HoldReasons (ReasonCode);
ALTER TABLE `DebtorsMaster` CHANGE `CurrCode` `CurrCode` VARCHAR( 3 ) NOT NULL;

ALTER TABLE DebtorsMaster ADD FOREIGN KEY (CurrCode) REFERENCES Currencies (CurrAbrev);
ALTER TABLE DebtorsMaster ADD FOREIGN KEY (PaymentTerms) REFERENCES PaymentTerms (TermsIndicator);
ALTER TABLE DebtorsMaster ADD FOREIGN KEY (SalesType) REFERENCES SalesTypes (TypeAbbrev);
ALTER TABLE DiscountMatrix ADD FOREIGN KEY (SalesType) REFERENCES SalesTypes (TypeAbbrev);
ALTER TABLE EDIItemMapping ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE FreightCosts ADD FOREIGN KEY (LocationFrom) REFERENCES Locations (LocCode);

ALTER TABLE FreightCosts ADD FOREIGN KEY (ShipperID) REFERENCES Shippers (Shipper_ID);
ALTER TABLE GLTrans ADD FOREIGN KEY (Account) REFERENCES ChartMaster (AccountCode);
ALTER TABLE GLTrans ADD FOREIGN KEY (Type) REFERENCES SysTypes (TypeID);
ALTER TABLE GLTrans ADD FOREIGN KEY (PeriodNo) REFERENCES Periods (PeriodNo);
ALTER TABLE GRNs ADD FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID);
ALTER TABLE GRNs ADD FOREIGN KEY (PODetailItem) REFERENCES PurchOrderDetails (PODetailItem);

ALTER TABLE LocStock ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);
ALTER TABLE LocStock ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);

ALTER TABLE OrderDeliveryDifferencesLog ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);

ALTER TABLE OrderDeliveryDifferencesLog ADD FOREIGN KEY (DebtorNo,Branch) REFERENCES CustBranch (DebtorNo,BranchCode);
ALTER TABLE `OrderDeliveryDifferencesLog` ADD INDEX ( `OrderNo` );
ALTER TABLE OrderDeliveryDifferencesLog ADD FOREIGN KEY (OrderNo) REFERENCES SalesOrders (OrderNo);

ALTER TABLE Prices ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE Prices ADD FOREIGN KEY (CurrAbrev) REFERENCES Currencies (CurrAbrev);
ALTER TABLE Prices ADD FOREIGN KEY (TypeAbbrev) REFERENCES SalesTypes (TypeAbbrev);

ALTER TABLE PurchData ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE PurchData ADD FOREIGN KEY (SupplierNo) REFERENCES Suppliers (SupplierID);

ALTER TABLE PurchOrderDetails ADD FOREIGN KEY (OrderNo) REFERENCES PurchOrders (OrderNo);

ALTER TABLE PurchOrders ADD FOREIGN KEY (SupplierNo) REFERENCES Suppliers (SupplierID);
ALTER TABLE PurchOrders ADD FOREIGN KEY (IntoStockLocation) REFERENCES Locations (LocCode);

ALTER TABLE ReportColumns ADD FOREIGN KEY (ReportID) REFERENCES ReportHeaders (ReportID);

ALTER TABLE `SalesAnalysis` CHANGE `PeriodNo` `PeriodNo` SMALLINT( 6 ) DEFAULT '0' NOT NULL ;
ALTER TABLE SalesAnalysis ADD FOREIGN KEY (PeriodNo) REFERENCES Periods (PeriodNo);

ALTER TABLE SalesOrderDetails ADD FOREIGN KEY (OrderNo) REFERENCES SalesOrders (OrderNo);
ALTER TABLE SalesOrderDetails ADD FOREIGN KEY (StkCode) REFERENCES StockMaster (StockID);

ALTER TABLE `SalesOrders` DROP INDEX `BranchCode`;
ALTER TABLE `SalesOrders` ADD INDEX ( `BranchCode`,`DebtorNo` );

ALTER TABLE SalesOrders ADD FOREIGN KEY (BranchCode, DebtorNo) REFERENCES CustBranch (BranchCode, DebtorNo);
ALTER TABLE `SalesOrders` ADD INDEX ( `ShipVia` );
ALTER TABLE SalesOrders ADD FOREIGN KEY (ShipVia) REFERENCES Shippers (Shipper_ID);
ALTER TABLE SalesOrders ADD FOREIGN KEY (FromStkLoc) REFERENCES Locations (LocCode);

ALTER TABLE Shipments CHANGE ShiptRef ShiptRef INT(11) NOT NULL;
ALTER TABLE `ShipmentCharges` CHANGE `ShiptRef` `ShiptRef` INT( 11 ) NOT NULL;
ALTER TABLE ShipmentCharges ADD FOREIGN KEY (ShiptRef) REFERENCES Shipments (ShiptRef);
ALTER TABLE `ShipmentCharges` ADD INDEX ( `TransType` );
ALTER TABLE ShipmentCharges ADD FOREIGN KEY (TransType) REFERENCES SysTypes (TypeID);
ALTER TABLE ShipmentCharges ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);

ALTER TABLE `Shipments` ADD FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID);

ALTER TABLE `StockCheckFreeze` ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE `StockCheckFreeze` ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);

ALTER TABLE `StockCounts` ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE `StockCounts` ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);

ALTER TABLE `StockMaster` ADD FOREIGN KEY (CategoryID) REFERENCES StockCategory (CategoryID);

ALTER TABLE `StockMoves` ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE `StockMoves` ADD FOREIGN KEY (Type) REFERENCES SysTypes (TypeID);
ALTER TABLE `StockMoves` ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);
ALTER TABLE `StockMoves` ADD FOREIGN KEY (Prd) REFERENCES Periods (PeriodNo);

DELETE FROM SuppAllocs WHERE ID=4;

ALTER TABLE SuppAllocs ADD FOREIGN KEY (TransID_AllocFrom) REFERENCES SuppTrans (ID);
ALTER TABLE `SuppAllocs` ADD FOREIGN KEY (TransID_AllocTo) REFERENCES SuppTrans (ID);

ALTER TABLE `SuppTrans` ADD FOREIGN KEY (Type) REFERENCES SysTypes (TypeID);
ALTER TABLE `SuppTrans` ADD FOREIGN KEY (SupplierNo) REFERENCES Suppliers (SupplierID);
ALTER TABLE `SupplierContacts` ADD FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID);
ALTER TABLE `Suppliers` ADD FOREIGN KEY (CurrCode) REFERENCES Currencies (CurrAbrev);
ALTER TABLE `Suppliers` ADD FOREIGN KEY (PaymentTerms) REFERENCES PaymentTerms (TermsIndicator);

ALTER TABLE `Suppliers` CHANGE `TaxAuthority` `TaxAuthority` TINYINT DEFAULT '1' NOT NULL;

ALTER TABLE `Suppliers` ADD FOREIGN KEY (TaxAuthority) REFERENCES TaxAuthorities (TaxID);
ALTER TABLE `WOIssues` ADD FOREIGN KEY (WORef) REFERENCES WorksOrders (WORef);
ALTER TABLE `WOIssues` ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE `WOIssues` ADD FOREIGN KEY (WorkCentre) REFERENCES WorkCentres (Code);

ALTER TABLE `WORequirements` ADD FOREIGN KEY (WORef) REFERENCES WorksOrders (WORef);
ALTER TABLE `WORequirements` ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE `WORequirements` ADD FOREIGN KEY (WrkCentre) REFERENCES WorkCentres (Code);

ALTER TABLE `WWW_Users` ADD INDEX ( `DefaultLocation` );

ALTER TABLE `WWW_Users` ADD FOREIGN KEY (DefaultLocation) REFERENCES Locations (LocCode);

ALTER TABLE `WorkCentres` ADD FOREIGN KEY (Location) REFERENCES Locations (LocCode);

ALTER TABLE `WorksOrders` ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);
ALTER TABLE `WorksOrders` ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);

ALTER TABLE DebtorsMaster ADD DiscountCode char(2) NOT NULL default '';

commit;