/* Add primary keys for tables missing one*/
ALTER TABLE `certSkills` ADD PRIMARY KEY `certID_skillID_certLevelInt` (`certID`, `skillID`, `certLevelInt`);
ALTER TABLE `certMasteries` ADD PRIMARY KEY `typeID_certID_masteryLevel` (`typeID`, `certID`, `masteryLevel`);
ALTER TABLE `chrFactions` CHANGE `raceIDs` `raceID` int NULL AFTER `description`;
ALTER TABLE `industryActivityMaterials` ADD PRIMARY KEY `typeID_materialTypeID_activityID` (`typeID`, `materialTypeID`, `activityID`),
DROP INDEX `ix_industryActivityMaterials_typeID`, DROP INDEX `industryActivityMaterials_idx1`;
ALTER TABLE `industryActivityProbabilities` DROP INDEX `ix_industryActivityProbabilities_productTypeID`, DROP INDEX `ix_industryActivityProbabilities_typeID`;
ALTER TABLE `industryActivityProbabilities` ADD PRIMARY KEY `typeID_productTypeID` (`typeID`, `productTypeID`);
ALTER TABLE `industryActivityProducts` DROP INDEX `ix_industryActivityProducts_productTypeID`, DROP INDEX `ix_industryActivityProducts_typeID`;
ALTER TABLE `industryActivityProducts` ADD PRIMARY KEY `typeID_productTypeID` (`typeID`, `productTypeID`);
ALTER TABLE `industryActivityRaces` DROP INDEX `ix_industryActivityRaces_typeID`, DROP INDEX `ix_industryActivityRaces_productTypeID`;
ALTER TABLE `industryActivityRaces` ADD PRIMARY KEY `typeID_productTypeID` (`typeID`, `productTypeID`);
ALTER TABLE `industryActivitySkills` DROP INDEX `ix_industryActivitySkills_skillID`, DROP INDEX `ix_industryActivitySkills_typeID`,
DROP INDEX `industryActivitySkills_idx1`;
/* industryActivitySkills has duplicate rows - i've removed them using a temporary table*/
CREATE TABLE `industryActivitySkillsTmp` AS SELECT DISTINCT * FROM `industryActivitySkills`;
DELETE FROM `industryActivitySkills`;
INSERT INTO `industryActivitySkills` SELECT * FROM `industryActivitySkillsTmp`;
DROP TABLE `industryActivitySkillsTmp`;
ALTER TABLE `industryActivitySkills` ADD PRIMARY KEY `typeID_activityID_skillID` (`typeID`, `activityID`, `skillID`);
ALTER TABLE `skinShip` ADD PRIMARY KEY `skinID_typeID` (`skinID`, `typeID`), DROP INDEX `ix_skinShip_typeID`, DROP INDEX `ix_skinShip_skinID`;

/* need to change some values to NULL to be able to add foreign keys*/
UPDATE `invTypes` SET `graphicID` = NULL WHERE `graphicID` = 0;
UPDATE `chrFactions` SET `raceID` = 32 WHERE `raceID` in (9,15);
UPDATE `chrFactions` SET `militiaCorporationID` = NULL WHERE `militiaCorporationID` = '0';
UPDATE `crpNPCCorporations` AS crp LEFT JOIN `chrFactions` AS cf ON crp.factionID = cf.factionID SET crp.factionID = NULL WHERE crp.factionID>0 AND cf.factionID IS NULL;
update dgmExpressions as de left join invTypes as it on de.expressionTypeID = it.typeID set de.expressionTypeID = NULL where de.expressionTypeID>0 and it.typeID is null;
update dgmExpressions as de left join dgmAttributeTypes as da on de.expressionAttributeID = da.attributeID set de.expressionAttributeID = NULL where de.expressionAttributeID>0 and da.attributeID is null;
update chrAncestries as ca left join eveIcons as ei on ca.iconID = ei.iconID set ca.iconID = NULL where ca.iconId > 0 and ei.iconID is null;
update invItems as ii left join invNames as iv on ii.ownerID = iv.itemID set ii.ownerID = null where ii.ownerID > 0 and iv.itemID is null;
update invTraits set skillID = NULL where skillID = -1;

/* some faulty pointers need to be deleted so foreign keys can be created. No 'update ... set null' like above because these columns are primary keys.*/
delete ia from industryActivity as ia left join invTypes as it on ia.typeID = it.typeID where ia.typeId > 0 and it.typeID is null;
delete iam from industryActivityMaterials as iam left join invTypes as it on iam.typeID = it.typeID where iam.typeID > 0 and it.typeID is null;
delete iam from industryActivityMaterials as iam left join invTypes as it on iam.materialTypeID = it.typeID where iam.materialTypeID > 0 and it.typeID is null;
delete iap from industryActivityProbabilities as iap left join invTypes as it on iap.typeID = it.typeID where iap.typeID > 0 and it.typeID is null;
delete iap from industryActivityProducts as iap left join invTypes as it on iap.typeID = it.typeID where iap.typeID > 0 and it.typeID is null;
delete iap from industryActivityProducts as iap left join invTypes as it on iap.productTypeID = it.typeID where iap.productTypeID > 0 and it.typeID is null;
delete ias from industryActivitySkills as ias left join invTypes as it on ias.typeID = it.typeID where ias.typeID > 0 and it.typeID is null;
delete ib from industryBlueprints as ib left join invTypes as it on ib.typeID = it.typeID where ib.typeID > 0 and it.typeID is null;
delete from invItems where typeID <0;
delete ii from invItems as ii left join invNames as iv on ii.itemID = iv.itemID where ii.itemID > 0 and iv.itemID is null;
delete itm from invTypeMaterials as itm left join invTypes as it on itm.typeID = it.typeID where itm.typeID > 0 and it.typeID is null;
delete itm from invTypeMaterials as itm left join invTypes as it on itm.materialTypeID = it.typeID where itm.materialTypeID > 0 and it.typeID is null;

/* create foregin keys and indexes for them */
ALTER TABLE `invCategories` ADD INDEX `iconID` (`iconID`);
ALTER TABLE `invCategories` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invGroups` ADD INDEX `iconID` (`iconID`);
ALTER TABLE `invGroups` ADD FOREIGN KEY (`categoryID`) REFERENCES `invCategories` (`categoryID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invGroups` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTypes` ADD INDEX `marketGroupID` (`marketGroupID`), ADD INDEX `soundID` (`soundID`), ADD INDEX `graphicID` (`graphicID`);
ALTER TABLE `invTypes` ADD FOREIGN KEY (`groupID`) REFERENCES `invGroups` (`groupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTypes` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTypes` ADD FOREIGN KEY (`graphicID`) REFERENCES `eveGraphics` (`graphicID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invMarketGroups` ADD INDEX `parentGroupID` (`parentGroupID`), ADD INDEX `iconID` (`iconID`);
ALTER TABLE `invMarketGroups` ADD FOREIGN KEY (`parentGroupID`) REFERENCES `invMarketGroups` (`marketGroupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invMarketGroups` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTypes` ADD FOREIGN KEY (`marketGroupID`) REFERENCES `invMarketGroups` (`marketGroupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrRaces` ADD INDEX `iconID` (`iconID`);
ALTER TABLE `chrRaces` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTypes` ADD FOREIGN KEY (`raceID`) REFERENCES `chrRaces` (`raceID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `agtAgents` ADD INDEX `divisionID` (`divisionID`), ADD INDEX `agentTypeID` (`agentTypeID`);
ALTER TABLE `agtAgents` ADD FOREIGN KEY (`divisionID`) REFERENCES `crpNPCDivisions` (`divisionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `agtAgents` ADD FOREIGN KEY (`agentTypeID`) REFERENCES `agtAgentTypes` (`agentTypeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `agtAgents` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `agtResearchAgents` ADD FOREIGN KEY (`agentID`) REFERENCES `agtAgents` (`agentID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `agtResearchAgents` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `certCerts` ADD INDEX `groupID` (`groupID`);
ALTER TABLE `certCerts` ADD FOREIGN KEY (`groupID`) REFERENCES `invGroups` (`groupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `certMasteries` ADD INDEX `typeID` (`typeID`), ADD INDEX `certID` (`certID`);
ALTER TABLE `certMasteries` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `certMasteries` ADD FOREIGN KEY (`certID`) REFERENCES `certCerts` (`certID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `certSkills` ADD INDEX `certID` (`certID`);
ALTER TABLE `certSkills` ADD FOREIGN KEY (`skillID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `certSkills` ADD FOREIGN KEY (`certID`) REFERENCES `certCerts` (`certID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `chrAncestries` ADD INDEX `bloodlineID` (`bloodlineID`), ADD INDEX `iconID` (`iconID`);
ALTER TABLE `chrAncestries` ADD FOREIGN KEY (`bloodlineID`) REFERENCES `chrBloodlines` (`bloodlineID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrAncestries` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrAttributes` ADD INDEX `iconID` (`iconID`);
ALTER TABLE `chrAttributes` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrBloodlines` ADD INDEX `raceID` (`raceID`), ADD INDEX `shipTypeID` (`shipTypeID`), ADD INDEX `corporationID` (`corporationID`),
ADD INDEX `iconID` (`iconID`);
ALTER TABLE `chrBloodlines` ADD FOREIGN KEY (`raceID`) REFERENCES `chrRaces` (`raceID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrBloodlines` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrBloodlines` ADD FOREIGN KEY (`shipTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrBloodlines` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrFactions` ADD INDEX `raceID` (`raceID`), ADD INDEX `solarSystemID` (`solarSystemID`), ADD INDEX `corporationID` (`corporationID`),
ADD INDEX `militiaCorporationID` (`militiaCorporationID`), ADD INDEX `iconID` (`iconID`);
ALTER TABLE `chrFactions` ADD FOREIGN KEY (`raceID`) REFERENCES `chrRaces` (`raceID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrFactions` ADD FOREIGN KEY (`solarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrFactions` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `chrFactions` ADD FOREIGN KEY (`militiaCorporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE; 
ALTER TABLE `chrFactions` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporationDivisions` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporationDivisions` ADD FOREIGN KEY (`divisionID`) REFERENCES `crpNPCDivisions` (`divisionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporationResearchFields` ADD FOREIGN KEY (`skillID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporationResearchFields` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD INDEX `solarSystemID` (`solarSystemID`), ADD INDEX `investorID1` (`investorID1`), ADD INDEX `investorID2` (`investorID2`),
ADD INDEX `investorID3` (`investorID3`), ADD INDEX `investorID4` (`investorID4`), ADD INDEX `friendID` (`friendID`), ADD INDEX `enemyID` (`enemyID`);
ALTER TABLE `crpNPCCorporations` ADD INDEX `factionID` (`factionID`), ADD INDEX `iconID` (`iconID`);
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`solarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`friendID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`enemyID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`investorID1`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`investorID2`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`investorID3`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporations` ADD FOREIGN KEY (`investorID4`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporationTrades` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `crpNPCCorporationTrades` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dgmAttributeTypes` ADD INDEX `iconID` (`iconID`), ADD INDEX `unitID` (`unitID`), ADD INDEX `categoryID` (`categoryID`);
ALTER TABLE `dgmAttributeTypes` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmAttributeTypes` ADD FOREIGN KEY (`unitID`) REFERENCES `eveUnits` (`unitID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmAttributeTypes` ADD FOREIGN KEY (`categoryID`) REFERENCES `dgmAttributeCategories` (`categoryID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD INDEX `iconID` (`iconID`), ADD INDEX `durationAttributeID` (`durationAttributeID`), 
ADD INDEX `trackingSpeedAttributeID` (`trackingSpeedAttributeID`), ADD INDEX `dischargeAttributeID` (`dischargeAttributeID`),
ADD INDEX `rangeAttributeID` (`rangeAttributeID`), ADD INDEX `falloffAttributeID` (`falloffAttributeID`),
ADD INDEX `npcUsageChanceAttributeID` (`npcUsageChanceAttributeID`), ADD INDEX `npcActivationChanceAttributeID` (`npcActivationChanceAttributeID`),
ADD INDEX `fittingUsageChanceAttributeID` (`fittingUsageChanceAttributeID`);
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`durationAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`trackingSpeedAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`dischargeAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`rangeAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`falloffAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`npcUsageChanceAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`npcActivationChanceAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmEffects` ADD FOREIGN KEY (`fittingUsageChanceAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmExpressions` ADD FOREIGN KEY (`expressionTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmExpressions` ADD FOREIGN KEY (`expressionGroupID`) REFERENCES `invGroups` (`groupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmExpressions` ADD FOREIGN KEY (`expressionAttributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `dgmTypeAttributes` ADD FOREIGN KEY (`attributeID`) REFERENCES `dgmAttributeTypes` (`attributeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dgmTypeAttributes` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dgmTypeEffects` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivity` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivity` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityMaterials` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityMaterials` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityMaterials` ADD FOREIGN KEY (`materialTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityProbabilities` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityProbabilities` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityProbabilities` ADD FOREIGN KEY (`productTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityProducts` ADD INDEX `activityID` (`activityID`);
ALTER TABLE `industryActivityProducts` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityProducts` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityProducts` ADD FOREIGN KEY (`productTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityRaces` ADD INDEX `activityID` (`activityID`);
ALTER TABLE `industryActivityRaces` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityRaces` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivityRaces` ADD FOREIGN KEY (`productTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivitySkills` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivitySkills` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryActivitySkills` ADD FOREIGN KEY (`skillID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `industryBlueprints` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invContrabandTypes` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invContrabandTypes` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invControlTowerResources` ADD INDEX `factionID` (`factionID`);
ALTER TABLE `invControlTowerResources` ADD FOREIGN KEY (`controlTowerTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invControlTowerResources` ADD FOREIGN KEY (`resourceTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invControlTowerResources` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invItems` ADD INDEX `typeID` (`typeID`), ADD INDEX `flagID` (`flagID`);
ALTER TABLE `invItems` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invItems` ADD FOREIGN KEY (`flagID`) REFERENCES `invFlags` (`flagID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invItems` ADD FOREIGN KEY (`itemID`) REFERENCES `invNames` (`itemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invItems` ADD FOREIGN KEY (`locationID`) REFERENCES `invNames` (`itemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invItems` ADD FOREIGN KEY (`ownerID`) REFERENCES `invNames` (`itemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invMetaGroups` ADD INDEX `iconID` (`iconID`);
ALTER TABLE `invMetaGroups` ADD FOREIGN KEY (`iconID`) REFERENCES `eveIcons` (`iconID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invMetaTypes` ADD INDEX `parentTypeID` (`parentTypeID`), ADD INDEX `metaGroupID` (`metaGroupID`);
ALTER TABLE `invMetaTypes` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invMetaTypes` ADD FOREIGN KEY (`parentTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invMetaTypes` ADD FOREIGN KEY (`metaGroupID`) REFERENCES `invMetaGroups` (`metaGroupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invPositions` ADD FOREIGN KEY (`itemID`) REFERENCES `invNames` (`itemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invTraits` ADD INDEX `typeID` (`typeID`), ADD INDEX `skillID` (`skillID`), ADD INDEX `unitID` (`unitID`);
ALTER TABLE `invTraits` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invTraits` ADD FOREIGN KEY (`skillID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTraits` ADD FOREIGN KEY (`unitID`) REFERENCES `eveUnits` (`unitID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invTypeMaterials` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invTypeMaterials` ADD FOREIGN KEY (`materialTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invTypeReactions` ADD FOREIGN KEY (`reactionTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invTypeReactions` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `invUniqueNames` ADD FOREIGN KEY (`groupID`) REFERENCES `invGroups` (`groupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapConstellations` ADD INDEX `regionID` (`regionID`), ADD INDEX `factionID` (`factionID`);
ALTER TABLE `mapConstellations` ADD FOREIGN KEY (`regionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapConstellations` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapConstellationJumps` ADD INDEX `fromRegionID` (`fromRegionID`), ADD INDEX `toRegionID` (`toRegionID`);
ALTER TABLE `mapConstellationJumps` ADD FOREIGN KEY (`fromRegionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapConstellationJumps` ADD FOREIGN KEY (`toRegionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapConstellationJumps` ADD FOREIGN KEY (`fromConstellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapConstellationJumps` ADD FOREIGN KEY (`toConstellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`itemID`) REFERENCES `invNames` (`itemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`groupID`) REFERENCES `invGroups` (`groupID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`solarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`constellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`regionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapDenormalize` ADD FOREIGN KEY (`orbitID`) REFERENCES `mapCelestialStatistics` (`celestialID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapJumps` ADD INDEX `destinationID` (`destinationID`);
ALTER TABLE `mapLocationScenes` ADD INDEX `graphicID` (`graphicID`);
ALTER TABLE `mapLocationScenes` ADD FOREIGN KEY (`graphicID`) REFERENCES `eveGraphics` (`graphicID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapLocationScenes` ADD FOREIGN KEY (`locationID`) REFERENCES `mapRegions` (`regionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapLocationWormholeClasses` ADD INDEX `wormholeClassID` (`wormholeClassID`);
ALTER TABLE `mapRegionJumps` ADD FOREIGN KEY (`fromRegionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapRegionJumps` ADD FOREIGN KEY (`toRegionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapRegions` ADD INDEX `factionID` (`factionID`);
ALTER TABLE `mapRegions` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystemJumps` ADD INDEX `fromRegionID` (`fromRegionID`), ADD INDEX `fromConstellationID` (`fromConstellationID`),
ADD INDEX `toConstellationID` (`toConstellationID`), ADD INDEX `toRegionID` (`toRegionID`);
ALTER TABLE `mapSolarSystemJumps` ADD FOREIGN KEY (`fromRegionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystemJumps` ADD FOREIGN KEY (`fromConstellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystemJumps` ADD FOREIGN KEY (`fromSolarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystemJumps` ADD FOREIGN KEY (`toSolarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystemJumps` ADD FOREIGN KEY (`toConstellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystemJumps` ADD FOREIGN KEY (`toRegionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystems` ADD INDEX `factionID` (`factionID`), ADD INDEX `sunTypeID` (`sunTypeID`);
ALTER TABLE `mapSolarSystems` ADD FOREIGN KEY (`regionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystems` ADD FOREIGN KEY (`constellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystems` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `mapSolarSystems` ADD FOREIGN KEY (`sunTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `planetSchematicsPinMap` ADD FOREIGN KEY (`schematicID`) REFERENCES `planetSchematics` (`schematicID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `planetSchematicsPinMap` ADD FOREIGN KEY (`pinTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `planetSchematicsTypeMap` ADD FOREIGN KEY (`schematicID`) REFERENCES `planetSchematics` (`schematicID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `planetSchematicsTypeMap` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineStations` ADD INDEX `assemblyLineTypeID` (`assemblyLineTypeID`), ADD INDEX `stationTypeID` (`stationTypeID`);
ALTER TABLE `ramAssemblyLineStations` ADD FOREIGN KEY (`stationID`) REFERENCES `staStations` (`stationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineStations` ADD FOREIGN KEY (`assemblyLineTypeID`) REFERENCES `ramAssemblyLineTypes` (`assemblyLineTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineStations` ADD FOREIGN KEY (`stationTypeID`) REFERENCES `staStationTypes` (`stationTypeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineStations`ADD FOREIGN KEY (`solarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE SET NULL ON UPDATE CASCADE; 
ALTER TABLE `ramAssemblyLineStations` ADD FOREIGN KEY (`regionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineStations` ADD FOREIGN KEY (`ownerID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineTypeDetailPerCategory` ADD FOREIGN KEY (`assemblyLineTypeID`) REFERENCES `ramAssemblyLineTypes` (`assemblyLineTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineTypeDetailPerCategory` ADD FOREIGN KEY (`categoryID`) REFERENCES `invCategories` (`categoryID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineTypeDetailPerGroup` ADD FOREIGN KEY (`assemblyLineTypeID`) REFERENCES `ramAssemblyLineTypes` (`assemblyLineTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineTypeDetailPerGroup` ADD FOREIGN KEY (`groupID`) REFERENCES `invGroups` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramAssemblyLineTypes` ADD INDEX `activityID` (`activityID`);
ALTER TABLE `ramAssemblyLineTypes` ADD FOREIGN KEY (`activityID`) REFERENCES `ramActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramInstallationTypeContents` ADD FOREIGN KEY (`assemblyLineTypeID`) REFERENCES `ramAssemblyLineTypes` (`assemblyLineTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ramInstallationTypeContents` ADD FOREIGN KEY (`installationTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `skinLicense` ADD INDEX `skinID` (`skinID`);
ALTER TABLE `skinLicense`ADD FOREIGN KEY (`licenseTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE; 
ALTER TABLE `skinLicense` ADD FOREIGN KEY (`skinID`) REFERENCES `skins` (`skinID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `skinShip` ADD FOREIGN KEY (`skinID`) REFERENCES `skins` (`skinID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `skinShip` ADD FOREIGN KEY (`typeID`) REFERENCES `invTypes` (`typeID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `staOperations` ADD INDEX `activityID` (`activityID`), ADD INDEX `caldariStationTypeID` (`caldariStationTypeID`), 
ADD INDEX `minmatarStationTypeID` (`minmatarStationTypeID`), ADD INDEX `amarrStationTypeID` (`amarrStationTypeID`), 
ADD INDEX `gallenteStationTypeID` (`gallenteStationTypeID`), ADD INDEX `joveStationTypeID` (`joveStationTypeID`);
ALTER TABLE `staOperations` ADD FOREIGN KEY (`caldariStationTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staOperations` ADD FOREIGN KEY (`minmatarStationTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staOperations` ADD FOREIGN KEY (`amarrStationTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staOperations` ADD FOREIGN KEY (`gallenteStationTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staOperations` ADD FOREIGN KEY (`joveStationTypeID`) REFERENCES `invTypes` (`typeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staOperations` ADD FOREIGN KEY (`activityID`) REFERENCES `crpActivities` (`activityID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `staOperationServices` ADD FOREIGN KEY (`operationID`) REFERENCES `staOperations` (`operationID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `staOperationServices` ADD FOREIGN KEY (`serviceID`) REFERENCES `staServices` (`serviceID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `staStations` ADD FOREIGN KEY (`operationID`) REFERENCES `staOperations` (`operationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staStations` ADD FOREIGN KEY (`stationTypeID`) REFERENCES `staStationTypes` (`stationTypeID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staStations` ADD FOREIGN KEY (`corporationID`) REFERENCES `crpNPCCorporations` (`corporationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staStations` ADD FOREIGN KEY (`solarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staStations` ADD FOREIGN KEY (`constellationID`) REFERENCES `mapConstellations` (`constellationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staStations` ADD FOREIGN KEY (`regionID`) REFERENCES `mapRegions` (`regionID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `staStationTypes` ADD INDEX `operationID` (`operationID`);
ALTER TABLE `staStationTypes` ADD FOREIGN KEY (`operationID`) REFERENCES `staOperations` (`operationID`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `warCombatZones` ADD INDEX `centerSystemID` (`centerSystemID`), ADD INDEX `factionID` (`factionID`);
ALTER TABLE `warCombatZones` ADD FOREIGN KEY (`factionID`) REFERENCES `chrFactions` (`factionID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `warCombatZones` ADD FOREIGN KEY (`centerSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `warCombatZoneSystems` ADD INDEX `combatZoneID` (`combatZoneID`);
ALTER TABLE `warCombatZoneSystems` ADD FOREIGN KEY (`solarSystemID`) REFERENCES `mapSolarSystems` (`solarSystemID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `warCombatZoneSystems` ADD FOREIGN KEY (`combatZoneID`) REFERENCES `warCombatZones` (`combatZoneID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `trnTranslationLanguages` ADD INDEX `languageID` (`languageID`);

