Member Avatar for manhthaodn

Hi,

I want to create a procedure to delete Production.Product table by ProductID in AdvetureWorks Database.

How to do it? Because Table Product's too many relationship :(

THANKS!

  • 3 Contributors
  • 4 Replies
  • 194 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by manhthaodn

Dani AI

Generated

Short summary: the safe way is to identify every foreign key that references Production.Product, delete dependent rows in child tables (bottom-up), then delete the product row inside a transaction. was right to point out child rows must go first; 's cascade-delete idea is valid but changing FKs to ON DELETE CASCADE in a production OLTP database is risky unless you really want automatic removal of all history/transactions.

Steps to follow (test on a copy first):

  1. Backup / work on a dev copy.
  2. Discover every FK that references Production.Product (direct children).
  3. Build and run DELETE statements for those children inside a transaction (record what you delete). Repeat for deeper levels if a child has its own children.
  4. Delete Production.Product. Commit only after verifying rowcounts and that no unexpected data was removed.

Quick helper queries and a procedure skeleton you can adapt (SQL Server 2008):

List direct foreign keys that reference Production.Product:

SELECT fk.name AS FKName,
       OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ParentSchema,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       STUFF((
         SELECT ', ' + pc.name
         FROM sys.foreign_key_columns fkc2
         JOIN sys.columns pc ON pc.object_id = fkc2.parent_object_id AND pc.column_id = fkc2.parent_column_id
         WHERE fkc2.constraint_object_id = fk.object_id
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'') AS ParentColumns
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('Production.Product')
ORDER BY ParentSchema, ParentTable;

Build-and-run deletes for direct children (automates step for direct FKs; you should run the generated SQL with PRINT first, verify, then EXEC):

DECLARE @ProductID INT = 760; -- change
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql +
'DELETE T FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ' T
JOIN Production.Product P ON ' +
STUFF((
  SELECT ' AND T.' + QUOTENAME(pc.name) + ' = P.' + QUOTENAME(rc.name)
  FROM sys.foreign_key_columns fkc2
  JOIN sys.columns pc ON pc.object_id = fkc2.parent_object_id AND pc.column_id = fkc2.parent_column_id
  JOIN sys.columns rc ON rc.object_id = fkc2.referenced_object_id AND rc.column_id = fkc2.referenced_column_id
  WHERE fkc2.constraint_object_id = fk.object_id
  ORDER BY fkc2.constraint_column_id
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,5,'') + '
WHERE P.ProductID = ' + CAST(@ProductID AS NVARCHAR(12)) + ';
'
FROM sys.foreign_keys fk
JOIN sys.tables t ON t.object_id = fk.parent_object_id
WHERE fk.referenced_object_id = OBJECT_ID('Production.Product');

PRINT @sql; -- inspect carefully
-- EXEC sp_executesql @sql;
-- finally: DELETE FROM Production.Product WHERE ProductID = @ProductID;

Cautions and tips:

  • Run PRINT first and inspect the generated DELETE statements.
  • Wrap all DML in BEGIN TRAN / COMMIT / ROLLBACK with TRY/CATCH.
  • Check triggers, audit/history tables, and backups before deleting.
  • If you need repeatable cleanup across environments, build a stored-proc that logs deleted PKs and runs iteratively for deeper levels (or use a schema-aware script to walk FK graph).

If you paste the output of the FK-list query here, a concrete, tested delete-order script for your AdventureWorks copy can be provided.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

If there are FK's enforced, then you have to make sure you first delete all child records, before you can delete the product.

Member Avatar for manhthaodn

I've tried your way but don't delete data. As I know, must use Cascade Delete.

Member Avatar for Taywin

So you may not be doing it correctly. Here is a link to an explanation from someone who has already asked similar question on another forum. I can't explain better than that...

Member Avatar for manhthaodn

I tried but still not done.

Because Product table of the database Adventure Works has too many relationships!

Has anyone done this yet?

Database system: SQL SERVER 2008

Hi,

I want to create a procedure to delete Production.Product table by ProductID in AdvetureWorks Database.

How to do it? Because Table Product's too many relationship :(

THANKS!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.