SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_sys_md_create_fk_cpl]
as
	-- acteurs
	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_act_cpl_act_actors'
					)
	BEGIN
		ALTER TABLE [dbo].[act_cpl]  WITH CHECK ADD  CONSTRAINT [FK_act_cpl_act_actors] FOREIGN KEY([acpl_actor_id])
		REFERENCES [dbo].[act_actors] ([act_actor_id])

		ALTER TABLE [dbo].[act_cpl] CHECK CONSTRAINT [FK_act_cpl_act_actors]
	END

	-- contrats
	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_pol_cpl_pol_policies_situations'
					)
	BEGIN
		ALTER TABLE [dbo].[pol_cpl]  WITH CHECK ADD  CONSTRAINT [FK_pol_cpl_pol_policies_situations] FOREIGN KEY([pcpl_policy_situation_id])
		REFERENCES [dbo].[pol_policies_situations] ([pols_policy_situation_id])

		ALTER TABLE [dbo].[pol_cpl] CHECK CONSTRAINT [FK_pol_cpl_pol_policies_situations]
	END
	
	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_pol_cpl_grpusg_usages'
					)
	BEGIN
		ALTER TABLE [dbo].[pol_cpl]  WITH CHECK ADD  CONSTRAINT [FK_pol_cpl_grpusg_usages] FOREIGN KEY([pcpl_usage_id])
		REFERENCES [dbo].[grpusg_usages] ([usgu_usage_id])


		ALTER TABLE [dbo].[pol_cpl] CHECK CONSTRAINT [FK_pol_cpl_grpusg_usages]
	END	

	-- risques
	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_ris_cpl_grpcls_classes'
					)
	BEGIN
		ALTER TABLE [dbo].[ris_cpl]  WITH CHECK ADD  CONSTRAINT [FK_ris_cpl_grpcls_classes] FOREIGN KEY([rcpl_class_id])
		REFERENCES [dbo].[grpcls_classes] ([clsc_class_id])

		ALTER TABLE [dbo].[ris_cpl] CHECK CONSTRAINT [FK_ris_cpl_grpcls_classes]
	END

	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_ris_cpl_grpusg_usages'
					)
	BEGIN
		ALTER TABLE [dbo].[ris_cpl]  WITH CHECK ADD  CONSTRAINT [FK_ris_cpl_grpusg_usages] FOREIGN KEY([rcpl_usage_id])
		REFERENCES [dbo].[grpusg_usages] ([usgu_usage_id])


		ALTER TABLE [dbo].[ris_cpl] CHECK CONSTRAINT [FK_ris_cpl_grpusg_usages]
	END

	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_ris_cpl_grpzon_zones'
					)
	BEGIN
		ALTER TABLE [dbo].[ris_cpl]  WITH CHECK ADD  CONSTRAINT [FK_ris_cpl_grpzon_zones] FOREIGN KEY([rcpl_zone_id])
		REFERENCES [dbo].[grpzon_zones] ([zonz_zone_id])

		ALTER TABLE [dbo].[ris_cpl] CHECK CONSTRAINT [FK_ris_cpl_grpzon_zones]
	END

	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_ris_cpl_ris_risks_situations'
					)
	BEGIN
		ALTER TABLE [dbo].[ris_cpl]  WITH CHECK ADD  CONSTRAINT [FK_ris_cpl_ris_risks_situations] FOREIGN KEY([rcpl_risk_situation_id])
		REFERENCES [dbo].[ris_risks_situations] ([riss_risk_situation_id])

		ALTER TABLE [dbo].[ris_cpl] CHECK CONSTRAINT [FK_ris_cpl_ris_risks_situations]
	END


	-- sinistres
	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_claim_cpl_claim_claims'
					)
	BEGIN
		ALTER TABLE [dbo].[claim_cpl]  WITH CHECK ADD  CONSTRAINT [FK_claim_cpl_claim_claims] FOREIGN KEY([ccpl_claim_id])
		REFERENCES [dbo].[claim_claims] ([cl_claim_id])

		ALTER TABLE [dbo].[claim_cpl] CHECK CONSTRAINT [FK_claim_cpl_claim_claims]
	END

	-- sinistres - actors
	if not exists	( 
					SELECT	1 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_NAME ='FK_claim_actors_cpl_claim_actors'
					)
	BEGIN
		ALTER TABLE [dbo].[claim_actors_cpl]  WITH CHECK ADD  CONSTRAINT [FK_claim_actors_cpl_claim_actors] FOREIGN KEY([clactcpl_claim_id], [clactcpl_actor_id])
		REFERENCES [dbo].[claim_actors] ([clact_claim_id], [clact_actor_id])

		ALTER TABLE [dbo].[claim_actors_cpl] CHECK CONSTRAINT [FK_claim_actors_cpl_claim_actors]
	END


GO
