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