SQL Server’da Foreign Key İlişkilerini Listeleme: Kolonlar, Kurallar ve Güvenilmeyen Kısıtlamalar

Tanımadığınız bir veritabanını devraldığınızda, veri taşıma ya da arşivleme planladığınızda veya bir tablodan neden kayıt silinemediğini anlamaya çalıştığınızda ilk ihtiyacınız tablolar arasındaki ilişkilerin haritasıdır. Foreign key’ler bu haritayı veritabanının kendi içinde tutar; doğru sorguyla hangi tablonun hangisine, hangi kolonlarla ve hangi kurallarla bağlı olduğunu birkaç saniyede çıkarabilirsiniz.

Bu yazıda foreign key ilişkilerini listelemeyi, güvenilmeyen (untrusted) kısıtlamaları bulup düzeltmeyi, index’siz foreign key kolonlarını tespit etmeyi ve tabloları bağımlılık sırasına dizmeyi anlatıyoruz.

Tüm Foreign Key İlişkilerini Listelemek

Aşağıdaki sorgu, veritabanındaki her foreign key için ana (parent) ve bağlı (referenced) tabloyu, kolonları sırasıyla, silme ve güncelleme kurallarını ve kısıtlamanın durumunu döndürür. Çok kolonlu foreign key’ler tek satırda birleştirilir (STRING_AGG ... WITHIN GROUP SQL Server 2017 ve sonrasını gerektirir):

SELECT fk.name                                              AS constraint_name,
       OBJECT_SCHEMA_NAME(fk.parent_object_id) + N'.' + OBJECT_NAME(fk.parent_object_id)         AS child_table,
       STRING_AGG(pc.name, N', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id)                AS child_columns,
       OBJECT_SCHEMA_NAME(fk.referenced_object_id) + N'.' + OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       STRING_AGG(rc.name, N', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id)                AS parent_columns,
       fk.delete_referential_action_desc AS on_delete,
       fk.update_referential_action_desc AS on_update,
       fk.is_disabled,
       fk.is_not_trusted
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns AS pc ON pc.object_id = fkc.parent_object_id     AND pc.column_id = fkc.parent_column_id
JOIN sys.columns AS rc ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id
GROUP BY fk.name, fk.parent_object_id, fk.referenced_object_id,
         fk.delete_referential_action_desc, fk.update_referential_action_desc,
         fk.is_disabled, fk.is_not_trusted
ORDER BY child_table, constraint_name;

Yalnızca tek bir tablonun ilişkilerini görmek için WHERE fk.parent_object_id = OBJECT_ID(N'dbo.Siparis') OR fk.referenced_object_id = OBJECT_ID(N'dbo.Siparis') koşulunu ekleyin; böylece hem tablonun başvurduğu hem de ona başvuran tablolar listelenir.

Silme ve Güncelleme Kuralları

on_delete ve on_update kolonları, ana tablodaki bir satır silindiğinde ya da anahtarı değiştiğinde ne olacağını gösterir: NO_ACTION işlemi reddeder, CASCADE bağlı satırları da siler ya da günceller, SET_NULL ve SET_DEFAULT bağlı kolonu boşaltır ya da varsayılan değere çeker.

Güvenilmeyen (Untrusted) Foreign Key’ler

Bir foreign key NOCHECK ile devre dışı bırakılıp yeniden açıldığında ya da toplu yükleme sırasında kontrol atlandığında, SQL Server mevcut verinin kurala uyduğundan emin olamaz ve kısıtlamayı is_not_trusted = 1 olarak işaretler. Bu yalnızca bir veri kalitesi sorunu değildir: optimizer güvenilmeyen kısıtlamaları plan oluştururken kullanamaz. Örneğin gereksiz bir join’i plandan çıkarma (join elimination) imkânı kaybolur.

-- Güvenilmeyen ya da devre dışı foreign key'ler ve düzeltme komutları
SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) + N'.' + OBJECT_NAME(fk.parent_object_id) AS table_name,
       fk.name, fk.is_disabled, fk.is_not_trusted,
       N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + N'.'
         + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
         + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';' AS fix_command
FROM sys.foreign_keys AS fk
WHERE fk.is_not_trusted = 1 OR fk.is_disabled = 1;

Index’i Olmayan Foreign Key Kolonları

SQL Server foreign key kolonlarına otomatik index oluşturmaz. Ana tablodan bir satır silindiğinde ya da anahtar güncellendiğinde, SQL Server bağlı tabloda eşleşen satırları aramak zorundadır; index yoksa bağlı tablonun tamamı taranır. Bu, basit bir silme işleminin yavaşlamasına ve kilitlenmelere yol açabilir. Aşağıdaki sorgu, ilk kolonu foreign key’in ilk kolonuyla başlayan bir index’i olmayan foreign key’leri listeler:

SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) + N'.' + OBJECT_NAME(fk.parent_object_id) AS table_name,
       fk.name AS constraint_name,
       c.name  AS first_fk_column
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
     ON fkc.constraint_object_id = fk.object_id AND fkc.constraint_column_id = 1
JOIN sys.columns AS c
     ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
WHERE NOT EXISTS (SELECT 1
                  FROM sys.index_columns AS ic
                  WHERE ic.object_id = fkc.parent_object_id
                    AND ic.column_id = fkc.parent_column_id
                    AND ic.key_ordinal = 1)
ORDER BY table_name;

Her sonuç için index eklemek otomatik bir karar değildir; tabloya yazma yükünü artırır. Ana tablodan silme ya da sık join yapılan ilişkilerde index genellikle faydalıdır. Index tasarımının ayrıntıları için SQL Server index rehberimize bakabilirsiniz.

Tabloları Bağımlılık Sırasına Dizmek

Veri yüklerken önce ana tabloları, silerken önce bağlı tabloları işlemek gerekir. Aşağıdaki özyinelemeli sorgu her tabloya bir seviye verir: seviye 0 hiçbir tabloya başvurmayan tablolardır; yükleme sırası seviyeye göre artan, silme sırası azalandır.

WITH deps AS (
    SELECT DISTINCT parent_object_id AS child_id, referenced_object_id AS parent_id
    FROM sys.foreign_keys
    WHERE parent_object_id <> referenced_object_id      -- kendine başvuran ilişkileri hariç tut
),
levels AS (
    SELECT t.object_id, 0 AS lvl
    FROM sys.tables AS t
    WHERE NOT EXISTS (SELECT 1 FROM deps AS d WHERE d.child_id = t.object_id)
    UNION ALL
    SELECT d.child_id, l.lvl + 1
    FROM deps AS d
    JOIN levels AS l ON l.object_id = d.parent_id
    WHERE l.lvl < 50                                    -- döngüsel ilişkilere karşı güvenlik
)
SELECT OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS table_name,
       MAX(lvl) AS load_level
FROM levels
GROUP BY object_id
ORDER BY load_level, table_name;

Birbirine döngüsel olarak başvuran tablolar bu sorguda seviye alamaz; bu tablolar listede görünmüyorsa aralarındaki ilişkiyi ayrıca inceleyin.

Kontrol Listesi

• Tüm foreign key ilişkileri ve kuralları belgelendi.
• CASCADE içeren ilişkiler toplu silme öncesinde gözden geçirildi.
• Güvenilmeyen ve devre dışı kısıtlamalar bakım penceresinde WITH CHECK ile düzeltildi.
• Index’siz foreign key kolonları iş yüküne göre değerlendirildi.
• Yükleme ve silme sırası bağımlılık seviyelerine göre planlandı.