troubleshooting Question

Please help explain create table syntax and reason for each line

Avatar of Tom Knowlton
Tom KnowltonFlag for United States of America asked on
Microsoft SQL Server 2008
2 Comments1 Solution248 ViewsLast Modified:
Please explain why each line of code exists in this script.

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros