ACCESS_LEVELS_STRUCTURE.sql

-------------------------------------------------------------------------------------------------
-- ACCESS_LEVELS_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 1 FROM [sys].[tables] WHERE [name] =  N'ACCESS_LEVELS') BEGIN
	PRINT N'❌ TABLE [ACCESS_LEVELS] IS ALREADY EXISTS';
END ELSE BEGIN
	PRINT N'➕ TABLE [ACCESS_LEVELS] IS NOT EXISTS AND WILL BE CREATED';
    CREATE TABLE [ACCESS_LEVELS] (
		[UID] UNIQUEIDENTIFIER NOT NULL,
		[TITLE] NVARCHAR(30) NOT NULL,
	);
    PRINT N'➕ TABLE [ACCESS_LEVELS] WAS CREATED';
END;


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

-- UNIQUE
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'UQ_ACCESS_LEVELS_TITLE' AND [TABLE_NAME] =  N'ACCESS_LEVELS') BEGIN
    ALTER TABLE [ACCESS_LEVELS] ADD CONSTRAINT [UQ_ACCESS_LEVELS_TITLE] UNIQUE([TITLE])
    PRINT N'➕ UNIQUE KEY [UQ_ACCESS_LEVELS_TITLE] WAS CREATED';
END;

--DEFAULT
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_ACCESS_LEVELS_UID') BEGIN
	ALTER TABLE [ACCESS_LEVELS] ADD CONSTRAINT [DF_ACCESS_LEVELS_UID] DEFAULT (NEWID()) FOR [UID];
	PRINT N'➕ DEFAULT KEY [DF_ACCESS_LEVELS_UID] 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;