quote:

---

Originally posted by dba123

I took a look at the table script for my PurchaseDownload table.

I'm trying to do this which works, but I need to add back in that constraint but not sure of the right syntax to do so.

First, what I'm performing is this so far:

ALTER TABLE PurchaseDownload
DROP CONSTRAINT [FK_PurchaseDownload_ProductFile]

delete from ProductFile
where ProductID in (
select f.ProductID from ProductFile f
inner join product p ON p.ProductID = f.ProductID
inner join SellerStore s ON s.SellerStoreID = p.SellerStoreID
where s.SellerStoreID = 1000
and FileName like '%zip%')

-- I need to readd that constraint or check back in here to restore it

Here's the PurchaseDownload table create script currently:

USE [MyDB]
GO
/****** Object: Table [dbo].[PurchaseDownload] Script Date: 03/12/2007 10:13:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PurchaseDownload](
[PurchaseDownloadID] [bigint] IDENTITY(1,1) NOT NULL,
[PurchaseID] [bigint] NOT NULL,
[DownloadDate] [datetime] NOT NULL,
[IPAddress] [varchar](15) NOT NULL,
[BrowserInfo] [varchar](100) NOT NULL,
[ProductSourceStatusID] [int] NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PurchaseDownload_CreateDate] DEFAULT (getdate()),
[CreateUserID] [int] NOT NULL,
[UpdateDate] [datetime] NULL,
[UpdateUserID] [int] NULL,
[Active] [bit] NOT NULL DEFAULT (1),
[ProductFileID] [bigint] NULL,
CONSTRAINT [PK_PurchaseDownload] PRIMARY KEY CLUSTERED
(
[PurchaseDownloadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PurchaseDownload] WITH CHECK ADD CONSTRAINT [FK_PurchaseDownload_ProductFile] FOREIGN KEY([ProductFileID])
REFERENCES [dbo].[ProductFile] ([ProductFileID])
GO
ALTER TABLE [dbo].[PurchaseDownload] CHECK CONSTRAINT [FK_PurchaseDownload_ProductFile]
GO
ALTER TABLE [dbo].[PurchaseDownload] WITH CHECK ADD CONSTRAINT [FK_PurchaseDownload_ProductSourceStatus] FOREIGN KEY([ProductSourceStatusID])
REFERENCES [dbo].[ProductSourceStatus] ([ProductSourceStatusID])
GO
ALTER TABLE [dbo].[PurchaseDownload] CHECK CONSTRAINT [FK_PurchaseDownload_ProductSourceStatus]
GO
ALTER TABLE [dbo].[PurchaseDownload] WITH CHECK ADD CONSTRAINT [FK_PurchaseDownload_Purchase] FOREIGN KEY([PurchaseID])
REFERENCES [dbo].[Purchase] ([PurchaseID])
GO
ALTER TABLE [dbo].[PurchaseDownload] CHECK CONSTRAINT [FK_PurchaseDownload_Purchase]

how can I add that constraint that I dropped back in?

---