USERS_STRUCTURE.sql

-------------------------------------------------------------------------------------------------
-- USERS_STRUCTURE
-------------------------------------------------------------------------------------------------
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @IS_COMMIT BIT = 0;
-------------------------------------------------------------------------------------------------
USE VideoHosting;

BEGIN TRAN
PRINT N'➕ JOB IS STARTED';

IF EXISTS (SELECT * FROM [sys].[tables] WHERE [name] = N'USERS') BEGIN
	PRINT N'❌ TABLE [USERS] IS ALREADY EXISTS';
END ELSE BEGIN
	PRINT N'➕ TABLE [USERS] IS NOT EXISTS AND WILL BE CREATED';
    CREATE TABLE [USERS] (
		[UID] UNIQUEIDENTIFIER NOT NULL,
		[USERNAME] NVARCHAR(32) NOT NULL,
        [EMAIL] NVARCHAR(50) NOT NULL,
        [CREATED_DT] DATETIME NOT NULL,
        [BIRTHDAY_DT] DATE NOT NULL,
	);
    PRINT N'➕ TABLE [USERS] WAS CREATED';
END;

-- PRIMARY KEY
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_TYPE] = 'PRIMARY KEY' AND [TABLE_NAME] = N'USERS') BEGIN
    ALTER TABLE [dbo].[USERS] ADD CONSTRAINT [PK_USERS_UID] PRIMARY KEY CLUSTERED ([UID] ASC)
        PRINT N'➕ PRIMARY KEY [PK_USERS_UID] WAS CREATED';
END;

-- UNIQUE
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'UQ_USERS_USERNAME' AND [TABLE_NAME] = N'USERS') BEGIN
    ALTER TABLE [USERS] ADD CONSTRAINT [UQ_USERS_USERNAME] UNIQUE([USERNAME])
    PRINT N'➕ UNIQUE KEY [UQ_USERS_USERNAME] WAS CREATED';
END;
IF NOT EXISTS (SELECT * FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'UQ_USERS_EMAIL' AND [TABLE_NAME] = N'USERS') BEGIN
    ALTER TABLE [USERS] ADD CONSTRAINT [UQ_USERS_EMAIL] UNIQUE([EMAIL])
    PRINT N'➕ UNIQUE KEY [UQ_USERS_EMAIL] WAS CREATED';
END;

-- DEFAULT
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_USERS_UID') BEGIN
	ALTER TABLE [USERS] ADD CONSTRAINT [DF_USERS_UID] DEFAULT (NEWID()) FOR [UID];
	PRINT N'➕ DEFAULT KEY [DF_USERS_UID] WAS CREATED';
END;
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_USERS_CREATED_DT') BEGIN
    ALTER TABLE [USERS] ADD CONSTRAINT [DF_USERS_CREATED_DT] DEFAULT (GETDATE()) FOR [CREATED_DT];
    PRINT N'➕ DEFAULT KEY [DF_USERS_CREATED_DT] WAS CREATED';
END;

IF (@IS_COMMIT = 1) BEGIN
	COMMIT TRAN
	PRINT N'➕ STRUCTURE IS COMMITTED';
END ELSE BEGIN
    ROLLBACK TRAN
	PRINT N'❌ STRUCTURE IS ROLL-BACKED';
END;