Sql-server – Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

And here's how to generate the script @Shark showed for all the tables you want to drop. Let's say you have the following tables:

USE tempdb;
GO

CREATE TABLE dbo.z(z INT PRIMARY KEY); -- we won't delete this one

CREATE TABLE dbo.a
(
 a INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.z(z)
);

CREATE TABLE dbo.b
(
 b INT PRIMARY KEY, 
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

CREATE TABLE dbo.c
(
 c INT PRIMARY KEY, 
 b INT FOREIGN KEY REFERENCES dbo.b(b), 
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

-- we won't drop this table either, but we'll need to drop
-- the constraint:

CREATE TABLE dbo.d
(
 d INT, 
 c INT FOREIGN KEY REFERENCES dbo.c(c)
);

But we only want to delete a, b, and c.

-- load the tables you want to delete into a table variable:

DECLARE @tables_to_delete TABLE (t NVARCHAR(512));

INSERT @tables_to_delete VALUES('dbo.a'),('dbo.b'),('dbo.c');


DECLARE @sql NVARCHAR(MAX) = N'';

-- build a list of the foreign keys you'll have to drop first:

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE ' 
    + QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
    + ' DROP CONSTRAINT ' + QUOTENAME(f.name) + ';'
FROM sys.foreign_keys AS f
INNER JOIN @tables_to_delete AS t
ON f.referenced_object_id = OBJECT_ID(t.t);

-- then the DROP TABLE commands:

SELECT @sql += CHAR(13) + CHAR(10) + N'DROP TABLE '
    + t + ';'
FROM @tables_to_delete;

PRINT @sql;
-- EXEC sp_executesql @sql;

Result (the constraint names will look different if you run this):

ALTER TABLE [dbo].[b] DROP CONSTRAINT [FK__b__a__2D27B809];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__a__30F848ED];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__b__300424B4];
ALTER TABLE [dbo].[d] DROP CONSTRAINT [FK__d__c__32E0915F];
DROP TABLE dbo.a;
DROP TABLE dbo.b;
DROP TABLE dbo.c;

When you're happy about the result, uncomment the EXEC line.

(Note, you won't be able to validate the script in its entirety when using PRINT if the script is very large. The script is truncated by Management Studio because it still has an archaic limit to how many characters it will show. The string won't be truncated like this when it gets passed to sp_executesql.)