CREATE TABLE [dbo].[pr_families]
(
[pf_family_code] [dbo].[ais_code_short] NOT NULL,
[pf_l1] [nvarchar] (64) COLLATE French_CI_AS NOT NULL,
[pf_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_rowver] [timestamp] NOT NULL,
[pf_class_code] [dbo].[ais_code_short] NOT NULL,
[pf_subclass_code] [dbo].[ais_code_short] NOT NULL,
[pf_freeze] [bit] NOT NULL CONSTRAINT [DF__pr_families__pf_freeze] DEFAULT ((0)),
[pf_policy_scen_code] [dbo].[ais_code_short] NULL,
[pf_risk_scen_code] [dbo].[ais_code_short] NULL,
[pf_claim_scen_code] [dbo].[ais_code_short] NULL,
[pf_group_code__class] [dbo].[ais_code_short] NULL,
[pf_group_code__zone] [dbo].[ais_code_short] NULL,
[pf_group_code__usage] [dbo].[ais_code_short] NULL,
[pf_sequence_code__prospects] [dbo].[ais_code_short] NOT NULL,
[pf_sequence_code__clients] [dbo].[ais_code_short] NOT NULL,
[pf_type_pol_code] [dbo].[ais_code_short] NOT NULL,
[pf_bonus] [bit] NOT NULL CONSTRAINT [DF__pr_families__pf_bonus] DEFAULT ((0)),
[pf_culture_code__default_cli] [dbo].[ais_code_short] NULL,
[pf_delegation] [bit] NOT NULL CONSTRAINT [DF__pr_families__pf_delegation] DEFAULT ((1)),
[pf_tariff_client_required] [bit] NOT NULL CONSTRAINT [DF__pr_families__pf_tariff_client_required] DEFAULT ((0)),
[pf_field_riss_number_l1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_field_riss_number_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_field_riss_number_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_field_riss_nature_l1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_field_riss_nature_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_field_riss_nature_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[pf_risk_nature_required] [bit] NOT NULL CONSTRAINT [DF__pr_families__pf_risk_nature_required] DEFAULT ((0)),
[pf_index] [bit] NOT NULL CONSTRAINT [DF__pr_families__pf_index] DEFAULT ((0)),
[pf_index_code] [dbo].[ais_code_medium] NULL,
[pf_short_code] [dbo].[ais_code_short] NULL,
[pf_renew_type_code] [dbo].[ais_code_short] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [CK_pr_families_bonus_index] CHECK ((([pf_bonus]=(0) AND [pf_index]=(0)) OR [pf_bonus]<>[pf_index]))
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [CK_pr_families_index] CHECK (([pf_index]=(0) OR [pf_index]=(1) AND [pf_index_code] IS NOT NULL))
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [CK__pr_families_pf_family_code__string_pk] CHECK ((len(ltrim(rtrim([pf_family_code])))>(0) AND charindex('*',[pf_family_code])=(0) AND charindex('%',[pf_family_code])=(0)))
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [PK_pr_families] PRIMARY KEY CLUSTERED  ([pf_family_code]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_dyn_scenarios] FOREIGN KEY ([pf_policy_scen_code]) REFERENCES [dbo].[dyn_scenarios] ([sc_scen_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_dyn_scenarios1] FOREIGN KEY ([pf_risk_scen_code]) REFERENCES [dbo].[dyn_scenarios] ([sc_scen_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_dyn_scenarios2] FOREIGN KEY ([pf_claim_scen_code]) REFERENCES [dbo].[dyn_scenarios] ([sc_scen_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_grpcls_groups] FOREIGN KEY ([pf_group_code__class]) REFERENCES [dbo].[grpcls_groups] ([clsg_group_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_grpusg_groups] FOREIGN KEY ([pf_group_code__usage]) REFERENCES [dbo].[grpusg_groups] ([usgg_group_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_grpzon_groups] FOREIGN KEY ([pf_group_code__zone]) REFERENCES [dbo].[grpzon_groups] ([zong_group_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_md_cultures] FOREIGN KEY ([pf_culture_code__default_cli]) REFERENCES [dbo].[md_cultures] ([clt_culture_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_nm_indices] FOREIGN KEY ([pf_index_code]) REFERENCES [dbo].[nm_indices] ([idx_index_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_nm_pol_renew_types] FOREIGN KEY ([pf_renew_type_code]) REFERENCES [dbo].[nm_pol_renew_types] ([nmprt_renew_type_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_nm_types_pol] FOREIGN KEY ([pf_type_pol_code]) REFERENCES [dbo].[nm_types_pol] ([nmtp_type_pol_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_pol_classes] FOREIGN KEY ([pf_class_code]) REFERENCES [dbo].[pol_classes] ([cls_class_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_pol_subclasses] FOREIGN KEY ([pf_class_code], [pf_subclass_code]) REFERENCES [dbo].[pol_subclasses] ([scls_class_code], [scls_subclass_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_sq_sequences] FOREIGN KEY ([pf_sequence_code__prospects]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[pr_families] ADD CONSTRAINT [FK_pr_families_sq_sequences1] FOREIGN KEY ([pf_sequence_code__clients]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
EXEC sp_addextendedproperty N'MS_Description', N'Soit toutes les deux 0 soit différentes, on ne peut pas avoir bonus ans index dans le même temps', 'SCHEMA', N'dbo', 'TABLE', N'pr_families', 'CONSTRAINT', N'CK_pr_families_bonus_index'
GO
