Spis treści:

Kategoria:IndeksySQL Server


Jak zamienić indeks nieklastrowany na klastrowany - obsługa kluczy obcych

Problem kluczy obcych

W poprzednim wpisie pisałem, jak można sobie wygenerować skrypt służący do zamiany indeksu nieklastrowanego (a w zasadzie klucza głównego) na klastrowany. Skrypt był prosty, ale niezwykle ograniczony i ułomny. Działał tylko wtedy, gdy nie było do niego żadnych relacji, czyli wtedy, gdy nie było żadnych kluczy obcych odnoszących się do tego klucza głównego nieklastrowanego. Gdy pojawiały się klucze obce, naszym oczom ukazywał się następujący komunikat:

Msg 3725, Level 16, State 0, Line 1
The constraint 'Klucz' is being referenced by table 'TabelaObca', foreign key constraint 'Klucz_Obcy'.

Plan działania będzie tym razem inny. W pierwszej kolejności usunięte zostaną wszystkie klucze obce wskazujące na modyfikowaną kolumnę klucza w tabeli, następnie zmodyfikowany zostanie sam klucz głowny, a w trzecim etapie zostaną ponownie założone wszystkie klucze obce. Wydaje się, że jest to proste i nie wymaga dalszego tłumaczenia. Skrypt może wygląć następująco:

DECLARE @inputTable nvarchar(128) = 'Lista_Semestrow'
DECLARE @inputSchema nvarchar(128) = 'dbo'

CREATE TABLE #scripts
(
 script NVARCHAR(1024)
)

INSERT INTO #scripts SELECT 'ALTER TABLE '+
PS.name+'.'+PT.name+' DROP CONSTRAINT '+FK.name
FROM sys.foreign_keys FK
JOIN sys.foreign_key_columns FKC ON FK.[object_id]=FKC.[constraint_object_id]
JOIN sys.columns PC ON FKC.parent_object_id = PC.[object_id]
    AND FKC.parent_column_id=PC.column_id
JOIN sys.tables PT ON PC.[object_id]=PT.[object_id]
JOIN sys.schemas PS ON PS.[schema_id]=PT.[schema_id]
JOIN sys.columns RC ON FKC.referenced_object_id = RC.[object_id]
    AND FKC.referenced_column_id=RC.column_id
JOIN sys.tables RT ON RC.[object_id]=RT.[object_id]
JOIN sys.schemas RS ON RS.[schema_id]=RT.[schema_id]
WHERE PS.name=@inputSchema AND RT.name=@inputTable

DECLARE @schema nvarchar(128)
DECLARE @table nvarchar(128)
DECLARE @key nvarchar(128)
DECLARE @column nvarchar(256)
SELECT @schema=S.name, @table=T.name, @key=K.name, @column=C.name
FROM sys.key_constraints K
JOIN sys.tablesON K.[parent_object_id]=T.[object_id]
JOIN sys.schemasON S.[schema_id]=T.[schema_id]
JOIN sys.index_columns I ON I.[object_id] = T.[object_id]
    AND K.unique_index_id=I.index_id
JOIN sys.columnsON C.[object_id]=T.[object_id] AND C.column_id=I.column_id
WHERE K.[type]='PK' AND S.name=@inputSchema AND T.name=@inputTable

INSERT INTO #scripts SELECT 'ALTER TABLE '+
  @schema+'.'+@table+' DROP CONSTRAINT '+@key
INSERT INTO #scripts SELECT 'ALTER TABLE '+
  @schema+'.'+@table+' ADD CONSTRAINT PK_'+@key+
  ' PRIMARY KEY CLUSTERED ('+@column+')'

INSERT INTO #scripts SELECT 'ALTER TABLE '+
  PS.name+'.'+PT.name+' ADD CONSTRAINT FK_'+PT.name+'_'+RT.name+
  ' FOREIGN KEY ('+PC.name+') REFERENCES '+RS.name+'.'+RT.name+'('+RC.name+')'
FROM sys.foreign_keys FK
JOIN sys.foreign_key_columns FKC ON FK.[object_id]=FKC.[constraint_object_id]
JOIN sys.columns PC ON FKC.parent_object_id = PC.[object_id]
    AND FKC.parent_column_id=PC.column_id
JOIN sys.tables PT ON PC.[object_id]=PT.[object_id]
JOIN sys.schemas PS ON PS.[schema_id]=PT.[schema_id]
JOIN sys.columns RC ON FKC.referenced_object_id = RC.[object_id]
    AND FKC.referenced_column_id=RC.column_id
JOIN sys.tables RT ON RC.[object_id]=RT.[object_id]
JOIN sys.schemas RS ON RS.[schema_id]=RT.[schema_id]
WHERE PS.name=@inputSchema AND RT.name=@inputTable

SELECTFROM #scripts
DROP TABLE #scripts

Co się może nie udać?

Podobne pytanie padło na końcu ostatniego wpisu. Także i tym razem skrypt nie jest idealny. Przypuśćmy, że klucz główny składa się z dwóch kolumn. Co się stanie? Złączenia pokazane w przykładowym kodzie podwoją nam ilość rekordów zwracanych przez zapytanie, a sam algorytm przestanie działać właściwie. Jak sobie z tym poradzić? O tym może innym razem.

Kategoria:IndeksySQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !