CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_NOM            CHAR(32)     NOT NULL,
 USR_PRENOM         VARCHAR(16)  NOT NULL,
 USR_MAIL           VARCHAR(256) NOT NULL,
 USR_ID             INTEGER      NOT NULL,
 CONSTRAINT UK_USR_MAIL UNIQUE (USR_MAIL),
 CONSTRAINT PK_USR      PRIMARY KEY (USR_ID));

CREATE TABLE S_ADM.T_DNS
(DNS_ID             INTEGER PRIMARY KEY,
 DNS_NAME           VARCHAR(128) NOT NULL UNIQUE);

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD USR_USER_EMAIL NVARCHAR(128) NOT NULL,
       DNS_ID INTEGER NOT NULL REFERENCES S_ADM.T_DNS(DNS_ID);

UPDATE S_ADM.T_UTILISATEUR_USR
   SET USR_USER_EMAIL = 
          SUBSTRING(USR_MAIL, 1, LEN(USR_MAIL)-CHARINDEX('@', USR_MAIL)-1),
       DNS_ID = (SELECT DNS_ID 
                 FROM S_ADM.T_DNS 
                 WHERE DNS_NAME = 
                    SUBSTRING(USR_MAIL, 
                              CHARINDEX('@', USR_MAIL) + 1, 
                                       LEN(USR_MAIL) 
                                       - CHARINDEX('@', USR_MAIL)));

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD CONSTRAINT UK_USR_MAIL UNIQUE (USR_USER_EMAIL, DNS_ID);

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   DROP COLUMN USR_MAIL CASCADE;


