CREATE TABLE [dbo].[nm_countries_postcodes]
(
[nmpc_country_code] [dbo].[ais_code_short] NOT NULL,
[nmpc_dpi_code] [dbo].[ais_code_short] NOT NULL,
[nmpc_postcode_code] [dbo].[ais_code_medium] NOT NULL,
[nmpc_order] [smallint] NOT NULL,
[nmpc_l1] [nvarchar] (64) COLLATE French_CI_AS NOT NULL,
[nmpc_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[nmpc_l3] [nvarchar] (64) COLLATE French_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[nm_countries_postcodes] ADD 
CONSTRAINT [PK_nm_countries_postcodes] PRIMARY KEY CLUSTERED  ([nmpc_country_code], [nmpc_dpi_code], [nmpc_postcode_code], [nmpc_order]) ON [PRIMARY]
ALTER TABLE [dbo].[nm_countries_postcodes] ADD
CONSTRAINT [FK_nm_countries_postcodes_nm_countries_departments] FOREIGN KEY ([nmpc_country_code], [nmpc_dpi_code]) REFERENCES [dbo].[nm_countries_departments] ([nmctrd_country_code], [nmctrd_dpi_code])

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