
IF EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
           WHERE 
             id = object_id(N'[dbo].[CustomerEANS]') AND 
             OBJECTPROPERTY(id, N'IsUserTable') = 1) 
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
               WHERE 
                 id = object_id(N'[dbo].[CustomerEANSDef]') AND 
                 OBJECTPROPERTY(id, N'IsUserTable') = 1) 

BEGIN
  CREATE TABLE dbo.CustomerEANSDef(
    EANType TINYINT NOT NULL,
	EANText_Default VARCHAR(50) NOT NULL,
	EANText VARCHAR(50) NULL,
	EANDescription VARCHAR(200) NULL
	)
	
  ALTER TABLE CustomerEANSDef ADD CONSTRAINT PK_CustomerEANSDef
  PRIMARY KEY (EANType)
END

IF NOT EXISTS(SELECT * FROM CustomerEANSDef)
BEGIN
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (0, 'Default')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (1, 'EAN 2')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (2, 'EAN 3')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (3, 'EAN 4')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (4, 'EAN 5')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (5, 'EAN 6')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (6, 'EAN 7')
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (7, 'EAN 8')  
  INSERT INTO CustomerEANSDef (EANType, EANText_Default) VALUES (8, 'EAN 9')
END

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerEANS_EANTypeFKey]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerEANS]'))
BEGIN
  ALTER TABLE CustomerEANS ADD CONSTRAINT FK_CustomerEANS_EANTypeFKey
  FOREIGN KEY (EANType) REFERENCES CustomerEANSDef (EANType)
END
