QuestionsAnswersUsers2

/*
Run this script on:

        SERVER_NAME\ENV, PORTNUM.DATABASE_NAME    -  This database will be modified


You are recommended to back up your database before running this script

time and date of transactional operation goes here
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [Database_Name]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

CREATE TABLE [My_Schema].[Users](
[User_Id] [INT] IDENTITY(1,1) NOT NULL,
[User_First_Name] VARCHAR(50) NOT NULL,
[User_Last_Name] VARCHAR(50) NOT NULL,
[User_Middle_Name] VARCHAR(50) NOT NULL,
CONSTRAINT [My_Schema____Users___User_Id__pk1] PRIMARY KEY CLUSTERED
(
    [User_Id] ASC
) WITH (DATA_COMPRESSION=PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [Filegroup_Name]
)ON [Filegroup_Name]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
CREATE TABLE [My_Schema].[Questions](
[Questions_Id] [INT] IDENTITY(1,1) NOT NULL,
[Question_Description]VARCHAR(255) NOT NULL,
[Question_Type] VARCHAR(5) NOT NULL,
CONSTRAINT [My_Schema____Questions___Question_Id__pk1] PRIMARY KEY CLUSTERED
(
    Questions_Id ASC
)WITH (DATA_COMPRESSION=PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [Filegroup_Name]
)ON [Filegroup_Name]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
CREATE TABLE [My_Schema].[Answers](
[Answers_Id][INT]IDENTITY(1,1) NOT NULL,
[User_Id][INT] NOT NULL,
[Questions_Id] [INT] NOT NULL,
[Answer_Text][VARCHAR](255) NULL,
CONSTRAINT[My_Schema____Answers___Answers_Id__pk1] PRIMARY KEY CLUSTERED
(
    [Answers_Id] ASC
)WITH (DATA_COMPRESSION=PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [Filegroup_Name]
)ON [Filegroup_Name]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

ALTER TABLE [My_Schema].[Answers] WITH CHECK ADD CONSTRAINT [MySchema____Answers___Questions_Id_fk1] FOREIGN KEY ([Question_Id])
REFERENCES [My_Schema].[Questions]([Questions_Id])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [My_Schema].[Answers] WITH CHECK ADD CONSTRAINT [MySchema____Answers___Users_Id__fk1] FOREIGN KEY ([Users_Id])
REFERENCES [My_Schema].[Questions]([Users_Id])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO