-- Typo
ALTER TABLE T_TELEFON DROP CONSTRAINT IF EXISTS CK_Telefon_Alapertelemetett_Gondivselo;
ALTER TABLE T_EMAIL DROP CONSTRAINT IF EXISTS CK_Email_Alapertelemetett_Gondivselo;
GO

IF OBJECT_ID('CK_Telefon_Alapertelmezett_Gondviselo', 'C') IS NULL BEGIN
  ALTER TABLE T_TELEFON
  ADD CONSTRAINT CK_Telefon_Alapertelmezett_Gondviselo
  CHECK (TOROLT = 'T' OR C_GONDVISELOID IS NULL OR C_ALAPERTELMEZETT = 'T')
END
GO

IF OBJECT_ID('CK_Email_Alapertelmezett_Gondviselo', 'C') IS NULL BEGIN
  ALTER TABLE T_EMAIL
  ADD CONSTRAINT CK_Email_Alapertelmezett_Gondviselo
  CHECK (TOROLT = 'T' OR C_GONDVISELOID IS NULL OR C_ALAPERTELMEZETT = 'T')
END
GO 

IF OBJECT_ID('CK_Telefon_Alapertelmezett_IsPublic', 'C') IS NULL BEGIN
  ALTER TABLE T_TELEFON
  ADD CONSTRAINT CK_Telefon_Alapertelmezett_IsPublic
  CHECK (TOROLT = 'T' OR C_ISPUBLIC = 'F' OR (C_ISPUBLIC = 'T' AND C_ALAPERTELMEZETT = 'F'))
END
GO

IF OBJECT_ID('CK_Email_Alapertelmezett_IsPublic', 'C') IS NULL BEGIN
  ALTER TABLE T_EMAIL
  ADD CONSTRAINT CK_Email_Alapertelmezett_IsPublic
  CHECK (TOROLT = 'T' OR C_ISPUBLIC = 'F' OR (C_ISPUBLIC = 'T' AND C_ALAPERTELMEZETT = 'F'))
END
GO 

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_Email_FelhasznaloId_GondviseloId_TanevId_Telefonszam') BEGIN
  CREATE UNIQUE INDEX NCU_Email_FelhasznaloId_GondviseloId_TanevId_Telefonszam
     ON T_EMAIL (
        C_FELHASZNALOID
       ,C_GONDVISELOID
       ,C_TANEVID
       ,C_EMAILCIM
	     ,C_ISPUBLIC)
     WHERE TOROLT = 'F' 
END
GO
​
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_Email_FelhasznaloId_TanevId') BEGIN
  CREATE UNIQUE INDEX NCU_Email_FelhasznaloId_TanevId
     ON T_EMAIL (
        C_FELHASZNALOID
       ,C_TANEVID)
     WHERE 
       TOROLT = 'F' 
       AND C_ISPUBLIC = 'T' 
       AND C_GONDVISELOID IS NULL
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_Telefon_FelhasznaloId_GondviseloId_TanevId_Telefonszam') BEGIN
  CREATE UNIQUE INDEX NCU_Telefon_FelhasznaloId_GondviseloId_TanevId_Telefonszam
     ON T_TELEFON (
        C_FELHASZNALOID
       ,C_GONDVISELOID
       ,C_TANEVID
       ,C_TELEFONSZAM
	     ,C_ISPUBLIC)
     WHERE TOROLT = 'F' 
END
GO
​
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_Telefon_FelhasznaloId_TanevId') BEGIN
  CREATE UNIQUE INDEX NCU_Telefon_FelhasznaloId_TanevId
     ON T_TELEFON (
        C_FELHASZNALOID
       ,C_TANEVID)
     WHERE
       TOROLT = 'F' 
       AND C_ISPUBLIC = 'T' 
       AND C_GONDVISELOID IS NULL
END
GO