Advertisement
bluebunny72

ISO Country Code unique index

Jul 22nd, 2016
237
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.02 KB | None | 0 0
  1. IF NOT EXISTS(SELECT TOP 1 1 FROM sys.indexes WHERE name = 'IX_ILC_country_iso_country_code')
  2.   BEGIN
  3.     CREATE UNIQUE NONCLUSTERED INDEX IX_ILC_country_iso_country_code
  4.     ON dbo.country(iso_country_code ASC)
  5.     WHERE iso_country_code IS NOT NULL;
  6.   END
  7.  
  8. GO
  9.  
  10. IF NOT EXISTS (SELECT TOP 1 1 FROM ObjectMainMessages WHERE ObjectName = 'IX_ILC_country_iso_country_code' AND MessageType = 17)
  11.     BEGIN
  12.         DECLARE @NextMessageNo  INT
  13.  
  14.         SELECT @NextMessageNo   = ISNULL(MAX(messageno),799999)
  15.         FROM ApplicationMessages
  16.         WHERE MessageNo BETWEEN 800000 AND 999999
  17.  
  18.         SET @NextMessageNo = @NextMessageNo + 1
  19.  
  20.         --add main message 100093 = The &1 entered already exists.
  21.         EXEC AddObjectMainMessageSp 'IX_ILC_country_iso_country_code', 17, 100093, 16, 'ISO Country Code UQ violation', 0
  22.  
  23.         --add the substitution text
  24.         EXEC AddApplicationMessageSp @NextMessageNo, 'ISO Country Code', N'1033'
  25.  
  26.         --add substitution text to main message
  27.         EXEC AddObjectBuildMessageSp 'IX_ILC_country_iso_country_code', 17, 1, @NextMessageNo
  28. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement