AGE_RESTRICTIONS_STRUCTURE.sql

-------------------------------------------------------------------------------------------------
-- AGE_RESTRICTIONS_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'AGE_RESTRICTIONS') BEGIN
	PRINT N'❌ TABLE [AGE_RESTRICTIONS] IS ALREADY EXISTS';
END ELSE BEGIN
	PRINT N'➕ TABLE [AGE_RESTRICTIONS] IS NOT EXISTS AND WILL BE CREATED';
    CREATE TABLE [AGE_RESTRICTIONS] (
		[UID] UNIQUEIDENTIFIER NOT NULL,
		[AGE] TINYINT NOT NULL,
	);
    PRINT N'➕ TABLE [AGE_RESTRICTIONS] WAS CREATED';
END;

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

-- CONSTRAINT 
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'CK_AGE_RESTRICTIONS_AGE') BEGIN
	ALTER TABLE [AGE_RESTRICTIONS] ADD CONSTRAINT [CK_AGE_RESTRICTIONS_AGE] CHECK ([AGE]>=0 AND [AGE]<=18);
	PRINT N'➕ CONSTRAINT [CK_AGE_RESTRICTIONS_AGE] WAS CREATED';
END;

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

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