java/dbloader/src/main/java/be/bosa/dt/best/dbloader/PostGisLoader.java
/**
	 * Create primary/foreign keys and additional constraints
	 * 
	 * @throws SQLException 
	 */
Create primary/foreign keys and additional constraints


Create primary / foreign keys and additional constraints
private void addConstraints() throws SQLException {
		try(Connection conn = getConnection()) {
			Statement stmt = conn.createStatement();
			LOG.info("Set primary keys");
			stmt.execute("ALTER TABLE Address " +
				" ADD CONSTRAINT pkAddress PRIMARY KEY(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE Municipality " +
				" ADD CONSTRAINT pkMunicipality PRIMARY KEY(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE PartOfMunicipality " +
				" ADD CONSTRAINT pkPartOfMunicipality PRIMARY KEY(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE PostalInfo " +
				" ADD CONSTRAINT pkPostalInfo PRIMARY KEY(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE Street " +
				" ADD CONSTRAINT pkStreet PRIMARY KEY(identifier, minorVersionIdentifier)");
			LOG.info("Set foreign keys");
			stmt.execute("ALTER TABLE Address ADD CONSTRAINT fkAddressMunicipality "+
				" FOREIGN KEY (mIdentifier, mMinorVersionIdentifier) "+
				" REFERENCES Municipality(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE Address ADD CONSTRAINT fkAddressPartOfMunicipality " +
				" FOREIGN KEY (mpIdentifier, mpMinorVersionIdentifier) "+
				" REFERENCES PartOfMunicipality(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE Address ADD CONSTRAINT fkAddressPostalInfo " +
				" FOREIGN KEY (pIdentifier, pMinorVersionIdentifier) " +
				" REFERENCES PostalInfo(identifier, minorVersionIdentifier)");
			stmt.execute("ALTER TABLE Address ADD CONSTRAINT fkAddressStreet " +
				" FOREIGN KEY (sIdentifier, sMinorVersionIdentifier) "+
				" REFERENCES Street(identifier, minorVersionIdentifier)");
		}
	}
private void addConstraints ( ) throws SQLException { try ( Connection conn = getConnection ( ) ) { Statement stmt = conn . createStatement ( ) ; LOG . info ( "Set primary keys" ) ; stmt . execute ( "ALTER TABLE Address " + " ADD CONSTRAINT pkAddress PRIMARY KEY(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE Municipality " + " ADD CONSTRAINT pkMunicipality PRIMARY KEY(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE PartOfMunicipality " + " ADD CONSTRAINT pkPartOfMunicipality PRIMARY KEY(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE PostalInfo " + " ADD CONSTRAINT pkPostalInfo PRIMARY KEY(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE Street " + " ADD CONSTRAINT pkStreet PRIMARY KEY(identifier, minorVersionIdentifier)" ) ; LOG . info ( "Set foreign keys" ) ; stmt . execute ( "ALTER TABLE Address ADD CONSTRAINT fkAddressMunicipality " + " FOREIGN KEY (mIdentifier, mMinorVersionIdentifier) " + " REFERENCES Municipality(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE Address ADD CONSTRAINT fkAddressPartOfMunicipality " + " FOREIGN KEY (mpIdentifier, mpMinorVersionIdentifier) " + " REFERENCES PartOfMunicipality(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE Address ADD CONSTRAINT fkAddressPostalInfo " + " FOREIGN KEY (pIdentifier, pMinorVersionIdentifier) " + " REFERENCES PostalInfo(identifier, minorVersionIdentifier)" ) ; stmt . execute ( "ALTER TABLE Address ADD CONSTRAINT fkAddressStreet " + " FOREIGN KEY (sIdentifier, sMinorVersionIdentifier) " + " REFERENCES Street(identifier, minorVersionIdentifier)" ) ; } }
Create primary/foreign keys and additional constraints
Create primary / foreign keys and additional constraints
// add primary keys, indices and constraints after loading data, for performance //stmt.execute("ALTER TABLE Event " + //	" ADD CONSTRAINT pkEvent PRIMARY KEY(eventIdentifier)");