După cum spuneam, întreţinerea FK-urilor (spre mai multe tabele alternative de utilizatori) poate fi automatizată.

 

Să presupunem că ai 350 de tabele cu FK-uri spre tabela Users:

USE tempdb

CREATE TABLE Users (
    UserID int IDENTITY PRIMARY KEY,
    UserName nvarchar(50) NOT NULL UNIQUE
)

CREATE TABLE T1 (a int primary key, UserID int references Users(UserID))
CREATE TABLE T2 (a int primary key, UserID int references Users(UserID))
[...]
CREATE TABLE T350 (a int primary key, UserID int references Users(UserID))

Pentru cei care vor să testeze ideea, CREATE TABLE-urile de mai sus pot fi generate cu următorul query:

SELECT 'CREATE TABLE T'+CAST(n as varchar(10))+' (a int primary key, UserID int references Users(UserID))'
FROM (SELECT TOP 350 ROW_NUMBER() OVER (ORDER BY name) n FROM syscolumns) x

Ei, în acest caz, dacă vrem să ştergem pur şi simplu din tabela Users cu instrucţiunea "DELETE Users WHERE UserID=1", obţinem minunata eroare:

Msg 8621, Level 17, State 2, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.

Să presupunem că facem 5 tabele auxiliare cu useri (n-avem nevoie decât de UserID, nu şi de celelalte coloane din tabela Users):

CREATE TABLE Users1 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users2 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users3 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users4 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users5 (UserID int PRIMARY KEY REFERENCES Users)

GO
CREATE TRIGGER CopiazaUserID ON Users FOR INSERT
AS
IF @@ROWCOUNT>0 BEGIN
    SET NOCOUNT ON
    INSERT INTO Users1 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users2 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users3 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users4 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users5 (UserID) SELECT UserID FROM inserted
END

GO
CREATE TRIGGER StergeUserID ON Users INSTEAD OF DELETE
AS
IF @@ROWCOUNT>0 BEGIN
    SET NOCOUNT ON
    DELETE Users1 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users2 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users3 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users4 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users5 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users WHERE UserID IN (SELECT UserID FROM deleted)
END

În loc de trigger-ul "INSTEAD OF DELETE" puteam să facem CASCADE DELETE la cele 5 FK-uri, dar cred că ar funcţiona mai bine aşa.

Cu 5 tabele de user-i, dacă alocăm pe rând FK-urile la cea mai puţin utilizată, putem să ajungem până la vreo 1000 de tabele cu FK-uri fără să dea eroare. Pentru a modifica FK-urile existente, putem executa următoarea procedură:

CREATE PROCEDURE FixUserFKs
AS
SET NOCOUNT ON

BEGIN TRAN

DECLARE ForeignKeysList CURSOR LOCAL READ_ONLY FOR
SELECT OBJECT_NAME(f.object_id) as FKName,
    SCHEMA_NAME(t.schema_id) as SchemaName,
    t.name as TableName, c.name as ColumnName
FROM sys.foreign_keys f INNER JOIN sys.tables t ON f.parent_object_id=t.object_id
INNER JOIN sys.foreign_key_columns fc ON f.object_id=fc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id=t.object_id AND c.column_id=fc.parent_column_id
WHERE f.referenced_object_id=OBJECT_ID('Users')
ORDER BY TableName
IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

DECLARE @FKName sysname, @SchemaName sysname, @TableName sysname, @ColumnName sysname
OPEN ForeignKeysList
IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

WHILE 1=1 BEGIN
    FETCH NEXT FROM ForeignKeysList INTO @FKName, @SchemaName, @TableName, @ColumnName
    IF @@FETCH_STATUS<>0 BREAK

    DECLARE @ReferencedTable sysname
    SELECT @ReferencedTable=name FROM (
        SELECT TOP 1 name, (
            SELECT COUNT(*) FROM sys.foreign_keys f
            WHERE f.referenced_object_id=t.object_id
        ) cnt
        FROM sys.tables t
        WHERE name LIKE 'Users[1-5]'
        ORDER BY cnt, name
    ) X WHERE cnt<255
    IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

    IF @ReferencedTable IS NULL BEGIN
        RAISERROR ('Could not find any alternate Users table !',16,1)
        ROLLBACK
        RETURN
    END

    DECLARE @SQL nvarchar(4000)
    SET @SQL='ALTER TABLE '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)
        +' DROP CONSTRAINT '+QUOTENAME(@FKName)
    PRINT @SQL
    EXEC(@SQL)
    IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

    SET @SQL='ALTER TABLE '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)
        +' ADD CONSTRAINT '+QUOTENAME(@FKName)+' FOREIGN KEY ('+QUOTENAME(@ColumnName)+')'
        +' REFERENCES '+QUOTENAME(@ReferencedTable)+' (UserID)'
    PRINT @SQL
    EXEC(@SQL)
    IF @@ERROR<>0 BEGIN ROLLBACK RETURN END
END

CLOSE ForeignKeysList
DEALLOCATE ForeignKeysList

COMMIT

Putem executa această procedură şi mai târziu, dacă se creează noi FK-uri care referă tabela Users. De fapt, putem să le spunem dezvoltatorilor: "nu vă bateţi capul să alegeţi voi tabela alternativă potrivită; faceţi FK-urile noi către tabela Users, iar din când în când o să executăm această procedură ca să le mute pe alte tabele mai puţin folosite". Şi astfel putem să trăim fericiţi până la adânci bătrâneţi fără să ne mai doară problema cu numărul prea mare de FK-uri spre aceeaşi tabelă.

Pentru cei care au vrut să testeze soluţia de mai sus, puteţi să scăpaţi de sutele de tabele de test executând rezultatul query-ului:

SELECT 'DROP TABLE T'+CAST(n as varchar(10))
FROM (SELECT TOP 350 ROW_NUMBER() OVER (ORDER BY name) n FROM syscolumns) x

iar apoi:

DROP TABLE Users1, Users2, Users3, Users4, Users5
DROP TABLE Users

dar oricum, dacă le-aţi creat în tempdb, ele vor fi şterse automat la următoarea restartare a server-ului. 

Răzvan Socol
SQL Server MVP