CREATE TABLE [dbo].[adr_addresses]
(
[adr_address_id] [int] NOT NULL,
[adr_rowver] [timestamp] NOT NULL,
[adr_type_code] [dbo].[ais_code_short] NULL,
[adr_title_code] [dbo].[ais_code_short] NULL,
[adr_name] [nvarchar] (64) COLLATE French_CI_AS NOT NULL,
[adr_first_name] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adr_complete_name] AS (ltrim(rtrim([adr_name]))+rtrim(' '+ltrim(isnull([adr_first_name],'')))) PERSISTED,
[adr_address1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adr_address2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adr_address3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adr_postal_code] [dbo].[ais_code_medium] NULL,
[adr_city] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adr_country_code] [dbo].[ais_code_short] NULL,
[adr_dpi_code] [dbo].[ais_code_short] NULL,
[adr_mail] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adr_home_phone] [varchar] (20) COLLATE French_CI_AS NULL,
[adr_business_phone] [varchar] (20) COLLATE French_CI_AS NULL,
[adr_business_phone2] [varchar] (20) COLLATE French_CI_AS NULL,
[adr_mobile_phone] [varchar] (20) COLLATE French_CI_AS NULL,
[adr_fax] [varchar] (20) COLLATE French_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[adr_addresses] ADD
CONSTRAINT [FK_adr_addresses_nm_countries_departments] FOREIGN KEY ([adr_country_code], [adr_dpi_code]) REFERENCES [dbo].[nm_countries_departments] ([nmctrd_country_code], [nmctrd_dpi_code])
ALTER TABLE [dbo].[adr_addresses] ADD 
CONSTRAINT [PK_adr_addresses] PRIMARY KEY CLUSTERED  ([adr_address_id]) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_adr_addresses_adr_complete_name] ON [dbo].[adr_addresses] ([adr_complete_name]) ON [PRIMARY]



GO

ALTER TABLE [dbo].[adr_addresses] ADD CONSTRAINT [FK_adr_addresses_nm_titles] FOREIGN KEY ([adr_title_code]) REFERENCES [dbo].[nm_titles] ([nmtit_title_code])
GO
ALTER TABLE [dbo].[adr_addresses] ADD CONSTRAINT [FK_adr_addresses_nm_types_addresses] FOREIGN KEY ([adr_type_code]) REFERENCES [dbo].[nm_types_addresses] ([nmtadr_type_code])
GO
