CREATE TABLE [dbo].[act_branches_departments]
(
[brdep_branch_id] [int] NOT NULL,
[brdep_department_code] [dbo].[ais_code_medium] NOT NULL,
[brdep_l1] [nvarchar] (64) COLLATE French_CI_AS NOT NULL,
[brdep_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[brdep_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[brdep_short_code] [dbo].[ais_code_short] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[act_branches_departments] ADD 
CONSTRAINT [PK_act_branches_departments] PRIMARY KEY CLUSTERED  ([brdep_branch_id], [brdep_department_code]) ON [PRIMARY]
ALTER TABLE [dbo].[act_branches_departments] ADD
CONSTRAINT [FK_act_branches_departments_act_branches] FOREIGN KEY ([brdep_branch_id]) REFERENCES [dbo].[act_branches] ([br_actor_id])

ALTER TABLE [dbo].[act_branches_departments] ADD
CONSTRAINT [CK__act_branches_departments_brdep_department_code__string_pk] CHECK ((len(ltrim(rtrim([brdep_department_code])))>(0) AND charindex('*',[brdep_department_code])=(0) AND charindex('%',[brdep_department_code])=(0)))
GO
