Tom Knowlton
asked on
Please help explain create table syntax and reason for each line
Please explain why each line of code exists in this script.
I am not a very experienced T-SQL programmer.
Thank you,
Tom
I am not a very experienced T-SQL programmer.
Thank you,
Tom
USE [PP]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductMatrix_ProductType]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductMatrix]'))
ALTER TABLE [dbo].[ProductMatrix] DROP CONSTRAINT [FK_ProductMatrix_ProductType]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductMatrix_Task]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductMatrix]'))
ALTER TABLE [dbo].[ProductMatrix] DROP CONSTRAINT [FK_ProductMatrix_Task]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductMatrix_TaskWorkFlow]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductMatrix]'))
ALTER TABLE [dbo].[ProductMatrix] DROP CONSTRAINT [FK_ProductMatrix_TaskWorkFlow]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ProductMatrix_ProductMatrixSID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[ProductMatrix] DROP CONSTRAINT [DF_ProductMatrix_ProductMatrixSID]
END
GO
USE [PP]
GO
/****** Object: Table [dbo].[ProductMatrix] Script Date: 11/16/2012 10:52:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductMatrix]') AND type in (N'U'))
DROP TABLE [dbo].[ProductMatrix]
GO
USE [PP]
GO
/****** Object: Table [dbo].[ProductMatrix] Script Date: 11/16/2012 10:52:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductMatrix](
[ProductMatrixID] [int] IDENTITY(1,1) NOT NULL,
[ProductMatrixSID] [int] NOT NULL,
[TaskWorkFlowID] [int] NULL,
[TaskWorkFlowSID] [int] NULL,
[IDTemplate] [int] NULL,
[TaskID] [int] NULL,
[NewProductID] [int] NULL,
CONSTRAINT [PK_ProductMatrix] PRIMARY KEY CLUSTERED
(
[ProductMatrixID] ASC,
[ProductMatrixSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductMatrix] WITH CHECK ADD CONSTRAINT [FK_ProductMatrix_ProductType] FOREIGN KEY([NewProductID])
REFERENCES [dbo].[ProductType] ([ProductTypeID])
GO
ALTER TABLE [dbo].[ProductMatrix] CHECK CONSTRAINT [FK_ProductMatrix_ProductType]
GO
ALTER TABLE [dbo].[ProductMatrix] WITH CHECK ADD CONSTRAINT [FK_ProductMatrix_Task] FOREIGN KEY([TaskID])
REFERENCES [dbo].[Task] ([TaskID])
GO
ALTER TABLE [dbo].[ProductMatrix] CHECK CONSTRAINT [FK_ProductMatrix_Task]
GO
ALTER TABLE [dbo].[ProductMatrix] WITH CHECK ADD CONSTRAINT [FK_ProductMatrix_TaskWorkFlow] FOREIGN KEY([TaskWorkFlowID], [TaskWorkFlowSID])
REFERENCES [dbo].[TaskWorkFlow] ([TaskWorkFlowID], [TaskWorkFlowSID])
GO
ALTER TABLE [dbo].[ProductMatrix] CHECK CONSTRAINT [FK_ProductMatrix_TaskWorkFlow]
GO
ALTER TABLE [dbo].[ProductMatrix] ADD CONSTRAINT [DF_ProductMatrix_ProductMatrixSID] DEFAULT (right(@@servername,(1))) FOR [ProductMatrixSID]
GO
USE [pp]
GO
ALTER TABLE [dbo].ProductMatrix ADD CONSTRAINT [UK_ProductMatrix_All] UNIQUE NONCLUSTERED
(
[ProductMatrixID] ,
[ProductMatrixSID] ,
[TaskWorkFlowID] ,
[TaskWorkFlowSID] ,
[IDTemplate],
[TaskID] ,
[NewProductID]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
: )