--- POSTGRES version

-- This script solves inconsistent entrys in the database of the production system

-- a) deleting of incorrect entrys in jointables (only one foreignkey is seated)
-- b) deleting of entrys in jointables where one (or both) referenced object does not exist
-- c) setting of foreignkeys to null when referenced object does nor exist


-- DataQuality
---- Link Metadata -> SecurityConstraints
DELETE FROM jt_metadata_secconst WHERE fk_metadata IS NULL OR fk_secconstraints IS NULL;
DELETE FROM jt_metadata_secconst
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_secconst x2 WHERE x1.id = x2.fk_metadata)
	OR fk_secconstraints NOT IN 
		(SELECT x1.id FROM md_securityconstraints x1, jt_metadata_secconst x2 WHERE x1.id = x2.fk_secconstraints);

---- DQElement
UPDATE dq_element
	SET fk_dataquality = null
	WHERE fk_dataquality NOT IN (SELECT x1.id FROM dq_dataquality x1, dq_element x2 WHERE x1.id = x2.fk_dataquality);
	
---- ProcessStep
DELETE FROM jt_quality_procstep WHERE fk_quality IS NULL OR fk_procstep IS NULL;
DELETE FROM jt_quality_procstep
	WHERE fk_quality NOT IN 
		(SELECT x1.id FROM dq_dataquality x1, jt_quality_procstep x2 WHERE x1.id = x2.fk_quality)
	OR fk_procstep NOT IN 
		(SELECT x1.id FROM li_processstep x1, jt_quality_procstep x2 WHERE x1.id = x2.fk_procstep);
UPDATE li_processstep
	SET fk_processor = null
	WHERE fk_processor NOT IN (SELECT x1.id FROM ci_respparty x1, li_processstep x2 WHERE x1.id = x2.fk_processor);

---- Source
DELETE FROM jt_li_src_li_procstep WHERE fk_source IS NULL OR fk_procstep IS NULL;
DELETE FROM jt_li_src_li_procstep
	WHERE fk_source NOT IN 
		(SELECT x1.id FROM li_source x1, jt_li_src_li_procstep x2 WHERE x1.id = x2.fk_source)
	OR fk_procstep NOT IN 
		(SELECT x1.id FROM li_processstep x1, jt_li_src_li_procstep x2 WHERE x1.id = x2.fk_procstep);
UPDATE li_source
	SET fk_sourcecitation = null
	WHERE fk_sourcecitation NOT IN (SELECT x1.id FROM ci_citation x1, li_source x2 WHERE x1.id = x2.fk_sourcecitation);
UPDATE li_source
	SET fk_sourcereferencesystem = null
	WHERE fk_sourcereferencesystem NOT IN (SELECT x1.id FROM rs_identifier x1, li_source x2 WHERE x1.id = x2.fk_sourcereferencesystem);

-- Distribution
UPDATE md_distribution
	SET fk_metadata = null
	WHERE fk_metadata NOT IN (SELECT x1.id FROM md_metadata x1, md_distribution x2 WHERE x1.id = x2.fk_metadata);

---- Distribution -> Format
DELETE FROM jt_dist_distformat WHERE fk_distribution IS NULL OR fk_format IS NULL;
DELETE FROM jt_dist_distformat
	WHERE fk_distribution NOT IN 
		(SELECT x1.id FROM md_distribution x1, jt_dist_distformat x2 WHERE x1.id = x2.fk_distribution)
	OR fk_format NOT IN 
		(SELECT x1.id FROM md_format x1, jt_dist_distformat x2 WHERE x1.id = x2.fk_format);
	
---- Distribution -> DigitalTransferOptions
DELETE FROM jt_digtransopt_mediumformat WHERE fk_digtransopt IS NULL OR fk_mediumformat IS NULL;
DELETE FROM jt_digtransopt_mediumformat
	WHERE fk_digtransopt NOT IN 
		(SELECT x1.id FROM md_digtransferopt x1, jt_digtransopt_mediumformat x2 WHERE x1.id = x2.fk_digtransopt)
	OR fk_mediumformat NOT IN 
		(SELECT x1.id FROM md_mediumformatcode x1, jt_digtransopt_mediumformat x2 WHERE x1.id = x2.fk_mediumformat);
DELETE FROM jt_digtransopt_mediumname WHERE fk_digtransopt IS NULL OR fk_mediumname IS NULL;
DELETE FROM jt_digtransopt_mediumname
	WHERE fk_digtransopt NOT IN 
		(SELECT x1.id FROM md_digtransferopt x1, jt_digtransopt_mediumname x2 WHERE x1.id = x2.fk_digtransopt)
	OR fk_mediumname NOT IN 
		(SELECT x1.id FROM md_mediumnamecode x1, jt_digtransopt_mediumname x2 WHERE x1.id = x2.fk_mediumname);
UPDATE md_digtransferopt
	SET fk_distribution = null
	WHERE fk_distribution NOT IN (SELECT x1.id FROM md_distribution x1, md_digtransferopt x2 WHERE x1.id = x2.fk_distribution);
UPDATE md_digtransferopt
	SET fk_onlineresource = null
	WHERE fk_onlineresource  NOT IN (SELECT x1.id FROM ci_onlineresource x1, md_digtransferopt x2 WHERE x1.id = x2.fk_onlineresource);
			
---- Distribution -> Distributor
DELETE FROM jt_dist_distributor WHERE fk_distribution IS NULL OR fk_distributor IS NULL;
DELETE FROM jt_dist_distributor
	WHERE fk_distribution NOT IN 
		(SELECT x1.id FROM md_distribution x1, jt_dist_distributor x2 WHERE x1.id = x2.fk_distribution)
	OR fk_distributor NOT IN 
		(SELECT x1.id FROM md_distributor x1, jt_dist_distributor x2 WHERE x1.id = x2.fk_distributor);
UPDATE md_distributor
	SET fk_responsibleparty = null
	WHERE fk_responsibleparty NOT IN (SELECT x1.id FROM ci_respparty x1, md_distributor x2 WHERE x1.id = x2.fk_responsibleparty);
	
------ StandardOrderProcess
UPDATE md_standorderproc
	SET fk_distributor = null
	WHERE fk_distributor NOT IN (SELECT x1.id FROM md_distributor x1, md_standorderproc x2 WHERE x1.id = x2.fk_distributor);

-- Link Metadata -> ResponsibleParty
DELETE FROM jt_metadata_respparty WHERE fk_metadata IS NULL OR fk_respparty IS NULL;
DELETE FROM jt_metadata_respparty
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_respparty x2 WHERE x1.id = x2.fk_metadata)
	OR fk_respparty NOT IN 
		(SELECT x1.id FROM ci_respparty x1, jt_metadata_respparty x2 WHERE x1.id = x2.fk_respparty);

-- FeatureCataogueDescription
DELETE FROM jt_metadata_featcatdesc WHERE fk_metadata IS NULL OR fk_featcatdesc IS NULL;
DELETE FROM jt_metadata_featcatdesc
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_featcatdesc x2 WHERE x1.id = x2.fk_metadata)
	OR fk_featcatdesc NOT IN 
		(SELECT x1.id FROM md_featcatdesc x1, jt_metadata_featcatdesc x2 WHERE x1.id = x2.fk_featcatdesc);
UPDATE md_featcatdesc
	SET fk_citation = null
	WHERE fk_citation NOT IN (SELECT x1.id FROM ci_citation x1, md_featcatdesc x2 WHERE x1.id = x2.fk_citation);
	
---- FeatureTypes
UPDATE featuretypes
	SET fk_featcatdesc = null
	WHERE fk_featcatdesc NOT IN (SELECT x1.id FROM md_featcatdesc x1, featuretypes x2 WHERE x1.id = x2.fk_featcatdesc);

-- ApplicationSchemaInformation
DELETE FROM jt_metadata_appschemainf WHERE fk_metadata IS NULL OR fk_appschemainf IS NULL;
DELETE FROM jt_metadata_appschemainf
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_appschemainf x2 WHERE x1.id = x2.fk_metadata)
	OR fk_appschemainf NOT IN 
		(SELECT x1.id FROM md_applicationschemainformation x1, jt_metadata_appschemainf x2 WHERE x1.id = x2.fk_appschemainf);
UPDATE md_applicationschemainformation
	SET fk_citation = null
	WHERE fk_citation NOT IN (SELECT x1.id FROM ci_citation x1, md_applicationschemainformation x2 WHERE x1.id = x2.fk_citation);
	
-- PortrayalCatalogueReference
DELETE FROM jt_metadata_portcatref WHERE fk_metadata IS NULL OR fk_portcatref IS NULL;
DELETE FROM jt_metadata_portcatref
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_portcatref x2 WHERE x1.id = x2.fk_metadata)
	OR fk_portcatref NOT IN 
		(SELECT x1.id FROM md_portrayalcatref x1, jt_metadata_portcatref x2 WHERE x1.id = x2.fk_portcatref);
UPDATE md_portrayalcatref
	SET fk_citation = null
	WHERE fk_citation NOT IN (SELECT x1.id FROM ci_citation x1, md_portrayalcatref x2 WHERE x1.id = x2.fk_citation);

-- Identifier 
DELETE FROM jt_metadata_refsys WHERE fk_metadata IS NULL OR fk_refsys IS NULL;
DELETE FROM jt_metadata_refsys
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_refsys x2 WHERE x1.id = x2.fk_metadata)
	OR fk_refsys NOT IN 
		(SELECT x1.id FROM rs_identifier x1, jt_metadata_refsys x2 WHERE x1.id = x2.fk_refsys);
UPDATE rs_identifier
	SET fk_authority = null
	WHERE fk_authority NOT IN (SELECT x1.id FROM ci_citation x1, rs_identifier x2 WHERE x1.id = x2.fk_authority);
-- Link Metadata -> LegalConstraints
DELETE FROM jt_metadata_legalconst WHERE fk_metadata IS NULL OR fk_legalconstraint IS NULL;
DELETE FROM jt_metadata_legalconst
	WHERE fk_metadata NOT IN 
		(SELECT x1.id FROM md_metadata x1, jt_metadata_legalconst x2 WHERE x1.id = x2.fk_metadata)
	OR fk_legalconstraint NOT IN 
		(SELECT x1.id FROM md_legalconstraints x1, jt_metadata_legalconst x2 WHERE x1.id = x2.fk_legalconstraint);
			
-- VectorSpatialRepresentation
UPDATE md_vectorspatialreprenstation
	SET fk_metadata = null
	WHERE fk_metadata NOT IN (SELECT x1.id FROM md_metadata x1, md_vectorspatialreprenstation x2 WHERE x1.id = x2.fk_metadata);
			

-- Identification
---- Link Identification -> SecurityConstraints
DELETE FROM jt_ident_secconst WHERE fk_secconstraint IS NULL OR fk_identification IS NULL;
DELETE FROM jt_ident_secconst
	WHERE fk_secconstraint NOT IN 
		(SELECT x1.id FROM md_securityconstraints x1, jt_ident_secconst x2 WHERE x1.id = x2.fk_secconstraint)
	OR fk_identification NOT IN 
		(SELECT x1.id FROM md_identification x1, jt_ident_secconst x2 WHERE x1.id = x2.fk_identification);

---- Keyword
DELETE FROM jt_keywords_keyword WHERE fk_keywords IS NULL OR fk_keyword IS NULL;
DELETE FROM jt_keywords_keyword
	WHERE fk_keywords NOT IN 
		(SELECT x1.id FROM md_keywords x1, jt_keywords_keyword x2 WHERE x1.id = x2.fk_keywords)
	OR fk_keyword NOT IN 
		(SELECT x1.id FROM keyword x1, jt_keywords_keyword x2 WHERE x1.id = x2.fk_keyword);
DELETE FROM jt_ident_keywords WHERE fk_ident IS NULL OR fk_keywords IS NULL;
DELETE FROM jt_ident_keywords
		WHERE fk_ident NOT IN 
			(SELECT x1.id FROM md_identification x1, jt_ident_keywords x2 WHERE x1.id = x2.fk_ident)
		OR fk_keywords NOT IN 
			(SELECT x1.id FROM md_keywords x1, jt_ident_keywords x2 WHERE x1.id = x2.fk_keywords);
UPDATE md_keywords
	SET fk_thesaurus = null
	WHERE fk_thesaurus NOT IN (SELECT x1.id FROM ci_citation x1, md_keywords x2 WHERE x1.id = x2.fk_thesaurus);
---- Link Identification -> ResponsibleParty
DELETE FROM jt_ident_respparty WHERE fk_identification IS NULL OR fk_responsibleparty IS NULL;
DELETE FROM jt_ident_respparty
	WHERE fk_identification NOT IN 
		(SELECT x1.id FROM md_identification x1, jt_ident_respparty x2 WHERE x1.id = x2.fk_identification)
	OR fk_responsibleparty NOT IN 
		(SELECT x1.id FROM ci_respparty x1, jt_ident_respparty x2 WHERE x1.id = x2.fk_responsibleparty);

---- Usage
DELETE FROM jt_ident_usage WHERE fk_ident IS NULL OR fk_usage IS NULL;
DELETE FROM jt_ident_usage
	WHERE fk_ident NOT IN 
		(SELECT x1.id FROM md_identification x1, jt_ident_usage x2 WHERE x1.id = x2.fk_ident)
	OR fk_usage NOT IN 
		(SELECT x1.id FROM md_usage x1, jt_ident_usage x2 WHERE x1.id = x2.fk_usage);
UPDATE md_usage
	SET fk_usercontactinfo = null
	WHERE fk_usercontactinfo NOT IN (SELECT x1.id FROM ci_respparty x1, md_usage x2 WHERE x1.id = x2.fk_usercontactinfo);
	
---- Maintenance
DELETE FROM jt_ident_mainten WHERE fk_maintenance IS NULL OR fk_identification IS NULL;
DELETE FROM jt_ident_mainten
	WHERE fk_maintenance NOT IN 
		(SELECT x1.id FROM md_maintenanceinformation x1, jt_ident_mainten x2 WHERE x1.id = x2.fk_maintenance)
	OR fk_identification NOT IN 
		(SELECT x1.id FROM md_identification x1, jt_ident_mainten x2 WHERE x1.id = x2.fk_identification);

---- Link Identification -> LegalConstraints
DELETE FROM jt_ident_legalconst WHERE fk_identification IS NULL OR fk_legalconst IS NULL;
DELETE FROM jt_ident_legalconst
	WHERE fk_identification NOT IN 
		(SELECT x1.id FROM md_identification x1, jt_ident_legalconst x2 WHERE x1.id = x2.fk_identification)
	OR fk_legalconst NOT IN 
		(SELECT x1.id FROM md_legalconstraints x1, jt_ident_legalconst x2 WHERE x1.id = x2.fk_legalconst);
		
---- BrowseGraphic
UPDATE md_browsegraphic
	SET fk_ident = null
	WHERE fk_ident NOT IN (SELECT x1.id FROM md_identification x1, md_browsegraphic x2 WHERE x1.id = x2.fk_ident);


---- DataIdentification
------ VerticalExtent
UPDATE ex_verticalextent
	SET fk_dataident = null
	WHERE fk_dataident NOT IN (SELECT x1.id FROM md_dataidentification x1, ex_verticalextent x2 WHERE x1.id = x2.fk_dataident);
UPDATE ex_verticalextent
	SET fk_verticaldatum = null
	WHERE fk_verticaldatum  NOT IN (SELECT x1.id FROM rs_identifier x1, ex_verticalextent x2 WHERE x1.id = x2.fk_verticaldatum);
	
------ GeographicBoundingBox
UPDATE ex_geogrbbox
	SET fk_owner = null
	WHERE fk_owner NOT IN (SELECT x1.id FROM md_dataidentification x1, ex_geogrbbox x2 WHERE x1.id = x2.fk_owner)
	AND fk_owner NOT IN (SELECT x1.id FROM csw_serviceidentification x1, ex_geogrbbox x2 WHERE x1.id = x2.fk_owner);
	
------ GeographicDescription
UPDATE ex_geographicdescription
	SET fk_dataident = null
	WHERE fk_dataident NOT IN (SELECT x1.id FROM md_dataidentification x1, ex_geographicdescription x2 WHERE x1.id = x2.fk_dataident);

-------- OperatesOn
UPDATE operateson
	SET fk_dataidentification = null
	WHERE fk_dataidentification NOT IN (SELECT x1.id FROM md_dataidentification x1, operateson x2 WHERE x1.id = x2.fk_dataidentification);
	
------ BoundingPolygon
UPDATE ex_boundingpolygon
	SET fk_dataident = null
	WHERE fk_dataident NOT IN (SELECT x1.id FROM md_dataidentification x1, ex_boundingpolygon x2 WHERE x1.id = x2.fk_dataident);

------ TemporalExtent
UPDATE ex_temporalextent
	SET fk_dataident = null
	WHERE fk_dataident NOT IN (SELECT x1.id FROM md_dataidentification x1, ex_temporalextent x2 WHERE x1.id = x2.fk_dataident);

------ Resolution
UPDATE md_resolution
	SET fk_dataident = null	
	WHERE fk_dataident NOT IN (SELECT x1.id FROM md_dataidentification x1, md_resolution x2 WHERE x1.id = x2.fk_dataident);

------ TopicCategory
DELETE FROM jt_dataident_topiccat WHERE fk_dataident IS NULL OR fk_topiccategory IS NULL;
DELETE FROM jt_dataident_topiccat
	WHERE fk_dataident NOT IN 
		(SELECT x1.id FROM md_dataidentification x1, jt_dataident_topiccat x2 WHERE x1.id = x2.fk_dataident)
	OR fk_topiccategory NOT IN 
		(SELECT x1.id FROM md_topiccategorycode x1, jt_dataident_topiccat x2 WHERE x1.id = x2.fk_topiccategory);

------ SpatialRepresesentationType
DELETE FROM jt_dataident_spatialreptype WHERE fk_dataident IS NULL OR fk_spatialreptype IS NULL;
DELETE FROM jt_dataident_spatialreptype
	WHERE fk_dataident NOT IN 
		(SELECT x1.id FROM md_dataidentification x1, jt_dataident_spatialreptype x2 WHERE x1.id = x2.fk_dataident)
	OR fk_spatialreptype NOT IN 
		(SELECT x1.id FROM md_spatialreptypecode x1, jt_dataident_spatialreptype x2 WHERE x1.id = x2.fk_spatialreptype);


---- ServiceIdentification
UPDATE csw_serviceidentification
	SET fk_legalconst = null
	WHERE fk_legalconst NOT IN (SELECT x1.id FROM md_legalconstraints x1, csw_serviceidentification x2 WHERE x1.id = x2.fk_legalconst);
UPDATE csw_serviceidentification
	SET fk_secconst = null
	WHERE fk_secconst NOT IN (SELECT x1.id FROM md_securityconstraints x1, csw_serviceidentification x2 WHERE x1.id = x2.fk_secconst);
		
-- LegalConstraints
DELETE FROM jt_legalconst_accessconst WHERE fk_legalconst IS NULL OR fk_restrictcode IS NULL;
DELETE FROM jt_legalconst_accessconst
	WHERE fk_legalconst NOT IN 
		(SELECT x1.id FROM md_legalconstraints x1, jt_legalconst_accessconst x2 WHERE x1.id = x2.fk_legalconst)
	OR fk_restrictcode NOT IN 
		(SELECT x1.id FROM md_restrictioncode x1, jt_legalconst_accessconst x2 WHERE x1.id = x2.fk_restrictcode);
DELETE FROM jt_legalconst_useconst WHERE fk_legalconst IS NULL OR fk_restrictcode IS NULL;
DELETE FROM jt_legalconst_useconst
	WHERE fk_legalconst NOT IN 
		(SELECT x1.id FROM md_legalconstraints x1, jt_legalconst_useconst x2 WHERE x1.id = x2.fk_legalconst)
	OR fk_restrictcode NOT IN 
		(SELECT x1.id FROM md_restrictioncode x1, jt_legalconst_useconst x2 WHERE x1.id = x2.fk_restrictcode);
UPDATE otherconstraints
	SET fk_legalconstraints = null
	WHERE fk_legalconstraints NOT IN (SELECT x1.id FROM md_legalconstraints x1, otherconstraints x2 WHERE x1.id = x2.fk_legalconstraints);
------ ServiceVersion
UPDATE serviceversion
	SET fk_serviceident = null
	WHERE fk_serviceident NOT IN (SELECT x1.id FROM csw_serviceidentification x1, serviceversion x2 WHERE x1.id = x2.fk_serviceident);

------ OperationMetadata
UPDATE sv_operationmetadata
	SET fk_serviceident = null
	WHERE fk_serviceident NOT IN (SELECT x1.id FROM csw_serviceidentification x1, sv_operationmetadata x2 WHERE x1.id = x2.fk_serviceident);

-------- Link OperationMetadata -> OnlineResource
DELETE FROM jt_opmeta_onlineres WHERE fk_operation IS NULL OR fk_onlineresource IS NULL;
DELETE FROM jt_opmeta_onlineres
	WHERE fk_operation NOT IN 
		(SELECT x1.id FROM sv_operationmetadata x1, jt_opmeta_onlineres x2 WHERE x1.id = x2.fk_operation)
	OR fk_onlineresource NOT IN 
		(SELECT x1.id FROM ci_onlineresource x1, jt_opmeta_onlineres x2 WHERE x1.id = x2.fk_onlineresource);

-------- Link OperationMetadata -> DCPList
DELETE FROM jt_operation_dcp WHERE fk_operation IS NULL OR fk_dcp IS NULL;
DELETE FROM jt_operation_dcp
	WHERE fk_operation NOT IN 
		(SELECT x1.id FROM sv_operationmetadata x1, jt_operation_dcp x2 WHERE x1.id = x2.fk_operation)
	OR fk_dcp NOT IN 
		(SELECT x1.id FROM sv_dcplist x1, jt_operation_dcp x2 WHERE x1.id = x2.fk_dcp);

-------- Parameter
UPDATE sv_parameter
	SET fk_operation = null
	WHERE fk_operation NOT IN (SELECT x1.id FROM sv_operationmetadata x1, sv_parameter x2 WHERE x1.id = x2.fk_operation);
-------- Link OperationMetadata -> OerationName
DELETE FROM jt_operation_name WHERE fk_operation IS NULL OR fk_name IS NULL;
DELETE FROM jt_operation_name
	WHERE fk_operation NOT IN 
		(SELECT x1.id FROM sv_operationmetadata x1, jt_operation_name x2 WHERE x1.id = x2.fk_operation)
	OR fk_name NOT IN 
		(SELECT x1.id FROM operationnames x1, jt_operation_name x2 WHERE x1.id = x2.fk_name);

-- SecurityConstraints
DELETE FROM jt_secconst_classificationcode WHERE fk_secconst IS NULL OR fk_classificationcode IS NULL;
DELETE FROM jt_secconst_classificationcode
	WHERE fk_secconst NOT IN 
		(SELECT x1.id FROM md_securityconstraints x1, jt_secconst_classificationcode x2 WHERE x1.id = x2.fk_secconst)
	OR fk_classificationcode NOT IN 
		(SELECT x1.id FROM md_classificationcode x1, jt_secconst_classificationcode x2 WHERE x1.id = x2.fk_classificationcode);

-------- OperatesOn
UPDATE operateson
	SET fk_serviceidentification = null
	WHERE fk_serviceidentification NOT IN (SELECT x1.id FROM csw_serviceidentification x1, operateson x2 WHERE x1.id = x2.fk_serviceidentification);

---------- Link OperatesOn -> OperationNme
DELETE FROM jt_operation_operateson WHERE fk_operation IS NULL OR fk_operateson IS NULL;
DELETE FROM jt_operation_operateson
	WHERE fk_operation NOT IN 
		(SELECT x1.id FROM operationnames x1, jt_operation_operateson x2 WHERE x1.id = x2.fk_operation)
	OR fk_operateson NOT IN 
		(SELECT x1.id FROM operateson x1, jt_operation_operateson x2 WHERE x1.id = x2.fk_operateson);


-- general informations
-- Citation
UPDATE ci_citation
	SET fk_fileidentifier = null
	WHERE fk_fileidentifier NOT IN (SELECT x1.id FROM fileidentifier x1, ci_citation x2 WHERE x1.id = x2.fk_fileidentifier);
	
---- AlternateTitle
UPDATE alternatetitle
	SET fk_citation = null
	WHERE fk_citation NOT IN (SELECT x1.id FROM ci_citation x1, alternatetitle x2 WHERE x1.id = x2.fk_citation);

---- Series
UPDATE ci_series 
	SET fk_citation = null
	WHERE fk_citation NOT IN (SELECT x1.id FROM ci_citation x1, ci_series x2 WHERE x1.id = x2.fk_citation);
	
---- Link Citation -> ResponsibleParty
DELETE FROM jt_citation_respparty WHERE fk_citation IS NULL OR fk_respparty IS NULL;
DELETE FROM jt_citation_respparty
	WHERE fk_citation NOT IN 
		(SELECT x1.id FROM ci_citation x1, jt_citation_respparty x2 WHERE x1.id = x2.fk_citation)
	OR fk_respparty NOT IN 
		(SELECT x1.id FROM ci_respparty x1, jt_citation_respparty x2 WHERE x1.id = x2.fk_respparty);

-- ResponsibleParty
UPDATE ci_respparty
	SET fk_contact = null
	WHERE fk_contact NOT IN (SELECT x1.id FROM ci_contact x1, ci_respparty x2 WHERE x1.id = x2.fk_contact);
	
-- Contact
UPDATE ci_contact
	SET fk_address = null 
	WHERE fk_address NOT IN (SELECT x1.id FROM ci_address x1, ci_contact x2 WHERE x1.id = x2.fk_address);
UPDATE ci_contact
	SET fk_onlineresource = null
	WHERE fk_onlineresource NOT IN (SELECT x1.id FROM ci_onlineresource x1, ci_contact x2 WHERE x1.id = x2.fk_onlineresource);
	
---- Address
UPDATE ci_address
	SET fk_deliverypoint = null
	WHERE fk_deliverypoint NOT IN (SELECT x1.id FROM deliverypoint x1, ci_address x2 WHERE x1.id = x2.fk_deliverypoint);
UPDATE ci_address
	SET fk_email = null
	WHERE fk_email NOT IN (SELECT x1.id FROM electronicmailaddress x1, ci_address x2 WHERE x1.id = x2.fk_email);