SQL Server ON DELETE CASCADE: Bir Foreign Key Ayarı Nasıl Veritabanınızın Yarısını Silebilir?

Bazı konular var, teorisini yıllardır biliyorsunuz ama gerçek bir hata mesajına denk gelince yeniden düşünmeye başlıyorsunuz. Geçenlerde bir integration test çalıştırırken karşıma şu hata çıktı:

The DELETE statement conflicted with the REFERENCE constraint
"FK_analysis_events_applications_app_id".

The conflict occurred in database "email_verify_tests",
table "dbo.analysis_events", column 'app_id'.

İlk bakışta oldukça klasik bir foreign key hatası. Bir application kaydını silmeye çalışıyorum ama ona bağlı analysis_events kayıtları hâlâ duruyor. SQL Server da doğal olarak şunu söylüyor: "Bu kaydı silemezsin. Başka kayıtlar hâlâ bunu referans ediyor." Tam bu noktada akla gelen ilk çözümlerden biri şu oluyor:

ON DELETE CASCADE

Bir satırlık ayar. Sorunu çözüyor. Ama bazı durumlarda başka sorunların da kapısını açıyor. Çünkü CASCADE, doğru yerde oldukça kullanışlıyken yanlış yerde kullanıldığında tek bir DELETE sorgusunu beklemediğiniz kadar büyük bir operasyona dönüştürebiliyor.

Önce temel ilişkiyi kuralım

Basit bir örnek üzerinden gidelim. Bir uygulama tablomuz olsun:

CREATE TABLE Applications
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL
);

Bir de uygulamaya ait analiz kayıtlarımız:

CREATE TABLE AnalysisEvents
(
    Id INT PRIMARY KEY,
    AppId INT NOT NULL,
    EventType NVARCHAR(100) NOT NULL,

    CONSTRAINT FK_AnalysisEvents_Applications
        FOREIGN KEY (AppId)
        REFERENCES Applications(Id)
);

Verilerimiz de şöyle olsun:

Applications

Id   Name
---  ---------------
10   SMTPSeer API

AnalysisEvents

Id   AppId   EventType
---  ------  ----------------
1    10      EmailValidated
2    10      EmailRejected
3    10      QuotaExceeded

Şimdi şu sorguyu çalıştıralım:

DELETE FROM Applications
WHERE Id = 10;

SQL Server bu işlemi reddeder. Çünkü AnalysisEvents tablosundaki üç kayıt hâlâ Applications.Id = 10 kaydına bağlıdır. Bu davranış aslında bizi koruyan bir davranış. Veritabanı referential integrity, yani ilişkisel veri bütünlüğünü koruyor. Eğer parent kaydı silip child kayıtları bıraksaydı elimizde şöyle anlamsız bir veri kalırdı:

AnalysisEvents.AppId = 10

ama:

Applications.Id = 10

artık yok. Böyle kayıtlara genellikle orphan record denir. Foreign key'in temel görevlerinden biri de tam olarak bunu engellemektir.

ON DELETE CASCADE ne yapıyor?

Foreign key'i şu şekilde tanımlarsak:

CONSTRAINT FK_AnalysisEvents_Applications
    FOREIGN KEY (AppId)
    REFERENCES Applications(Id)
    ON DELETE CASCADE

davranış değişir. Artık:

DELETE FROM Applications
WHERE Id = 10;

çalıştırıldığında SQL Server sadece Applications tablosundaki kaydı silmez. O kayda bağlı AnalysisEvents kayıtlarını da otomatik olarak siler. Mantıksal olarak yapılan işlem kabaca şuna benzer:

DELETE FROM AnalysisEvents
WHERE AppId = 10;

DELETE FROM Applications
WHERE Id = 10;

Tabii bunu bizim manuel olarak yazmamız gerekmez. Foreign key davranışı bunu bizim yerimize gerçekleştirir. İşte cascade delete tam olarak budur:

Parent kayıt silindiğinde ona bağlı child kayıtların da otomatik olarak silinmesi.

Buraya kadar gayet güzel

Hatta çoğu geliştirici ilk gördüğünde şunu düşünebilir: "Neden bütün foreign key'lere cascade koymuyoruz?" Çünkü gerçek sistemlerde ilişkiler çoğu zaman iki tablodan ibaret değildir. Örneğin şöyle bir yapı düşünelim:

Tenant
└── Application
    └── AnalysisEvent
        └── AnalysisEventDetail

Eğer bütün ilişkiler ON DELETE CASCADE olarak tanımlanmışsa:

DELETE FROM Tenants
WHERE Id = 5;

tek bir tenant kaydını silerken aşağıdaki kayıtların tamamı silinebilir:

Tenant
Applications
AnalysisEvents
AnalysisEventDetails

Ve sistem biraz daha karmaşıksa zincir burada bitmeyebilir.

Tenant
├── Applications
│   ├── AnalysisEvents
│   │   └── AnalysisEventDetails
│   └── ApiKeys
├── Users
├── UsageRecords
└── Settings

Tek bir satır:

DELETE FROM Tenants
WHERE Id = 5;

beklediğinizden çok daha büyük bir veri setini etkileyebilir. Başlıktaki "veritabanının yarısını silmek" biraz mizahi bir ifade olsa da, cascade zincirlerinin büyük sistemlerde ciddi etkileri olabilir.

CASCADE neden bu kadar tehlikeli olabilir?

Asıl problem CASCADE özelliğinin kötü olması değil. Problem, silme işleminin etkisini gizleyebilmesidir. Şu sorguya baktığınızda:

DELETE FROM Applications
WHERE Id = 10;

gözünüz yalnızca Applications tablosunu görür. Ama foreign key tarafında cascade varsa gerçek etki farklıdır. Bir developer sorguya bakarak yalnızca bir satırın silineceğini düşünebilir. Gerçekte:

1 Application
12 AnalysisEvents
87 AnalysisEventDetails
4 ApiKeys

silinebilir. İşin tehlikeli tarafı da burada başlıyor. Uygulama kodunda açıkça görülmeyen bir davranış, database şemasında tanımlanmış olabilir.

Her ilişkide CASCADE kullanılmalı mı?

Bence burada sorulması gereken soru şu:

Parent kayıt silindiğinde child kayıtların varlığının herhangi bir anlamı kalıyor mu?

Örneğin:

Order
└── OrderItems

Bir sipariş gerçekten fiziksel olarak siliniyorsa, ona ait OrderItems kayıtlarının bağımsız olarak sistemde kalmasının çoğu senaryoda anlamı yoktur. Bu nedenle:

OrderItems.OrderId

üzerinde cascade kullanımı mantıklı olabilir. Başka bir örnek:

BlogPost
└── DraftBlocks

Taslak tamamen silindiğinde ona ait geçici blokların da silinmesi doğal olabilir. Ama her ilişki bu kadar net değildir.

Audit ve log tablolarında iki kere düşünmek gerekir

Benim karşılaştığım örnekte ilişki şuna benziyordu:

Application
└── AnalysisEvents

Burada önemli bir soru ortaya çıkıyor: Application silindiğinde geçmiş analiz kayıtları da gerçekten silinmeli mi? Eğer AnalysisEvents bir audit veya history tablosu niteliğindeyse cevap büyük ihtimalle "hayır" olabilir. Örneğin sistemde geçmişte şunları bilmek isteyebilirsiniz:

Bu tenant kaç doğrulama yaptı?
Hangi tarihlerde quota aşıldı?
Hangi API key hangi işlemleri gerçekleştirdi?
Bir hata hangi application üzerinden oluştu?

Application kaydı silinmiş olsa bile bu geçmiş bilgiler hâlâ değerli olabilir. Hatta bazı sistemlerde regülasyon, raporlama veya güvenlik sebebiyle bunların tutulması gerekebilir. Bu durumda cascade eklemek:

ON DELETE CASCADE

teknik olarak problemi çözerken iş kuralını bozabilir.

Foreign key hatası bazen faydalı bir alarmdır

Şu hatayı tekrar düşünelim:

The DELETE statement conflicted with the REFERENCE constraint...

İlk refleks genellikle: "Bu hatayı nasıl kaldırırım?" oluyor. Ama aslında bazen doğru soru şudur: "SQL Server neden bunu silmeme izin vermiyor?" Çünkü database size veri modeliniz hakkında bir şey söylüyor. Bir kayıt başka bir kayıt tarafından kullanılıyor. Belki önce child kayıtların silinmesi gerekiyor. Belki parent kayıt hiç fiziksel olarak silinmemeli. Belki foreign key yanlış kurulmuş. Belki de gerçekten cascade gerekiyor. Yani foreign key hatası her zaman düzeltilmesi gereken bir engel değildir. Bazen sistemin veri bütünlüğünü koruyan son bariyerdir.

Test ortamında CASCADE tuzağı

Benim örneğimde hata integration test sırasında ortaya çıkmıştı. Test sonunda muhtemelen database temizlenmeye çalışılıyordu:

DELETE FROM Applications;

ama AnalysisEvents kayıtları hâlâ duruyordu. Burada hızlı çözüm olarak production foreign key'ine:

ON DELETE CASCADE

eklemek cazip gelebilir. Ama yalnızca test cleanup kolaylaşsın diye production veri modelini değiştirmek doğru bir yaklaşım değil. Test cleanup kodu child tablolardan başlamalıdır. Örneğin:

DELETE FROM AnalysisEvents;
DELETE FROM Applications;

Daha karmaşık sistemlerde bu sıra daha uzun olabilir:

DELETE FROM AnalysisEventDetails;
DELETE FROM AnalysisEvents;
DELETE FROM ApiKeys;
DELETE FROM Applications;
DELETE FROM Tenants;

Yani:

Child → Parent

sırasıyla ilerlemek gerekir. Production domain davranışı neyse foreign key davranışı ona göre tasarlanmalıdır. Test altyapısı da buna uyum sağlamalıdır. Tersi değil.

ON DELETE NO ACTION

SQL Server'da foreign key silme davranışında en güvenli varsayımlardan biri NO ACTION yaklaşımıdır. Örneğin:

FOREIGN KEY (AppId)
REFERENCES Applications(Id)
ON DELETE NO ACTION

Parent kayıt child kayıtlar tarafından referans ediliyorsa SQL Server silme işlemini engeller. Pratikte bu bizim başta gördüğümüz davranıştır. Avantajı oldukça açıktır: Yanlışlıkla veri kaybetmek yerine hata alırsınız. Ben kritik veri içeren sistemlerde bu davranışı genellikle daha anlaşılır buluyorum. Çünkü silme operasyonunun ne yaptığı application katmanında açık şekilde görülebilir. Örneğin:

DELETE FROM AnalysisEvents
WHERE AppId = @AppId;

DELETE FROM Applications
WHERE Id = @AppId;

Kod biraz daha uzun olabilir. Ama neyin silindiği çok daha nettir.

ON DELETE SET NULL

Bir başka seçenek de:

ON DELETE SET NULL

kullanımıdır. Örneğin:

CREATE TABLE AnalysisEvents
(
    Id INT PRIMARY KEY,
    AppId INT NULL,

    CONSTRAINT FK_AnalysisEvents_Applications
        FOREIGN KEY (AppId)
        REFERENCES Applications(Id)
        ON DELETE SET NULL
);

Application silindiğinde event kaydı silinmez. Sadece:

AppId = NULL

olur. Örneğin önce:

Id   AppId   EventType
1    10      EmailValidated

varken application silindikten sonra:

Id   AppId   EventType
1    NULL    EmailValidated

kalabilir. Bu yaklaşım özellikle history ve audit benzeri yapılarda bazı senaryolarda işe yarayabilir. Fakat burada başka bir problem ortaya çıkar: Event hangi application'a aitti? Eğer sadece foreign key tutuyorsanız bu bilgi artık kaybolmuştur. Bu nedenle audit sistemlerinde yalnızca relational ID tutmak yerine bazı snapshot bilgileri de saklanabilir. Örneğin:

ApplicationId
ApplicationName
TenantId
EventType
CreatedAt

gibi. Bu tamamen domain ihtiyacına bağlıdır.

Soft delete çoğu zaman daha iyi bir seçenek olabilir

SaaS uygulamalarında benim daha sık gördüğüm modellerden biri soft delete yaklaşımıdır. Yani:

DELETE FROM Applications
WHERE Id = 10;

yerine:

UPDATE Applications
SET
    IsDeleted = 1,
    DeletedAt = SYSUTCDATETIME()
WHERE Id = 10;

kullanılır. Tablo örneği:

CREATE TABLE Applications
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL,
    IsDeleted BIT NOT NULL DEFAULT 0,
    DeletedAt DATETIME2 NULL
);

Uygulamadaki sorgular da:

SELECT *
FROM Applications
WHERE IsDeleted = 0;

şeklinde çalışır. Bunun birkaç avantajı vardır:

• ilişkiler bozulmaz,
• geçmiş veriler korunur,
• yanlış silmeler geri alınabilir,
• audit kayıtları application ile ilişkilendirilmeye devam eder.

Tabii soft delete'in de kendi maliyetleri vardır. Her sorguda silinmiş kayıtları filtrelemek gerekir. Unique constraint gibi alanlarda ekstra tasarım gerekebilir. Veri zamanla büyür. Gerçek silme için ayrıca archive veya purge mekanizması gerekebilir. Yani soft delete de her probleme otomatik çözüm değildir.

Cascade zincirlerini görmek neden önemli?

Bir database üzerinde çalışmaya başladığımda özellikle kritik tablolardaki foreign key ilişkilerine bakmayı faydalı buluyorum. Çünkü yalnızca tablo kolonlarını görmek her zaman yeterli olmuyor. Şunu bilmek daha önemli olabiliyor:

Bu kaydı silersem başka ne etkilenir?

Örneğin aşağıdaki sorguyla cascade tanımlı foreign key'leri görebilirsiniz:

SELECT
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
    fk.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys fk
WHERE fk.delete_referential_action_desc = 'CASCADE';

Sonuç örneği şöyle olabilir:

ForeignKeyName                       ChildTable            ParentTable     DeleteAction
-----------------------------------  --------------------  --------------  ------------
FK_AnalysisEvents_Applications       AnalysisEvents        Applications    CASCADE
FK_EventDetails_AnalysisEvents       AnalysisEventDetails  AnalysisEvents  CASCADE

Burada artık şunu daha net görebiliriz:

Applications
     ↓
AnalysisEvents
     ↓
AnalysisEventDetails

Bir application silmek aslında üç seviyeli bir delete zinciri oluşturuyor.

Bir DELETE çalıştırmadan önce kaç satır etkileneceğini bilmek

Özellikle manuel production müdahalelerinde doğrudan şunu çalıştırmak:

DELETE FROM Applications
WHERE TenantId = 123;

yerine önce:

SELECT *
FROM Applications
WHERE TenantId = 123;

çalıştırmak zaten temel bir alışkanlık olmalı. Cascade varsa bir adım daha ileri gitmek gerekir. Örneğin:

SELECT COUNT(*)
FROM Applications
WHERE TenantId = 123;

ardından:

SELECT COUNT(*)
FROM AnalysisEvents ae
INNER JOIN Applications a
    ON a.Id = ae.AppId
WHERE a.TenantId = 123;

ve gerekiyorsa:

SELECT COUNT(*)
FROM AnalysisEventDetails aed
INNER JOIN AnalysisEvents ae
    ON ae.Id = aed.AnalysisEventId
INNER JOIN Applications a
    ON a.Id = ae.AppId
WHERE a.TenantId = 123;

Böylece:

10 application siliyorum.

diye düşündüğünüz bir işlemin aslında:

10 Applications
3.500 AnalysisEvents
18.000 AnalysisEventDetails

etkilediğini önceden görebilirsiniz. Bu fark production ortamında oldukça önemlidir.

Transaction burada hayat kurtarabilir

Manuel veya kritik delete işlemlerinde transaction kullanmak da iyi bir güvenlik katmanıdır. Örneğin:

BEGIN TRANSACTION;

DELETE FROM Applications
WHERE Id = 10;

SELECT @@ROWCOUNT AS DeletedApplications;

ROLLBACK;

Özellikle cascade davranışını anlamaya çalışıyorsanız önce rollback ile test etmek faydalıdır. Her şey beklediğiniz gibiyse:

COMMIT;

ile gerçek işlemi gerçekleştirebilirsiniz. Elbette production ortamında büyük delete işlemleri transaction log, lock ve performans açısından ayrıca değerlendirilmelidir. Çok büyük cascade operasyonları tek transaction içerisinde ciddi yük oluşturabilir.

EF Core kullanıyorsanız konu biraz daha kafa karıştırabilir

.NET ve EF Core tarafında çalışıyorsanız cascade davranışının yalnızca SQL Server seviyesinde olmadığını da unutmamak gerekiyor. Entity ilişkileri tanımlanırken:

modelBuilder.Entity<AnalysisEvent>()
    .HasOne(x => x.Application)
    .WithMany(x => x.AnalysisEvents)
    .HasForeignKey(x => x.AppId)
    .OnDelete(DeleteBehavior.Cascade);

gibi bir yapı kullanabilirsiniz. Alternatif olarak:

.OnDelete(DeleteBehavior.Restrict);

veya:

.OnDelete(DeleteBehavior.SetNull);

gibi davranışlar tanımlanabilir. Burada migration çıktısını kontrol etmek önemli. Çünkü kod tarafında yaptığınız ilişki tanımı sonunda database schema'sına dönüşür. Migration dosyasında örneğin şunu görebilirsiniz:

onDelete: ReferentialAction.Cascade

Bu küçük satır production database üzerinde ciddi bir davranış değişikliği anlamına gelebilir. Ben özellikle ilişki değişiklikleri içeren migration'larda generated SQL veya migration içeriğini gözden geçirmeyi değerli buluyorum.

CASCADE kullanmadan önce kendime sorduğum sorular

Cascade kararı verirken birkaç sorunun oldukça işe yaradığını düşünüyorum. İlk soru:

Child kayıt parent olmadan anlamlı mı?

Cevap kesin olarak hayırsa cascade iyi bir aday olabilir. İkinci soru:

Child kayıt geçmiş veya audit bilgisi taşıyor mu?

Evetse cascade konusunda dikkatli olmak gerekir. Üçüncü soru:

Parent fiziksel olarak gerçekten silinecek mi?

Eğer sistem soft delete kullanıyorsa cascade zaten çok daha az devreye girecektir. Dördüncü soru:

Bu foreign key başka cascade zincirlerinin parçası mı?

Asıl risk çoğu zaman tek ilişki değil, zincirleme ilişkilerdir. Beşinci soru:

Bir developer yalnızca DELETE sorgusuna bakarak işlemin etkisini anlayabilir mi?

Cevap hayırsa en azından schema ve operasyon dokümantasyonu tarafında bunun görünür olması faydalıdır.

CASCADE kötü bir özellik değil

Burada yanlış anlaşılmaya açık bir nokta var. ON DELETE CASCADE kötü bir özellik değil. Aksine doğru veri modelinde oldukça kullanışlıdır. Problem:

CASCADE kullanmak

değil. Problem:

neden CASCADE kullandığını bilmeden kullanmak

oluyor. Örneğin temporary, bağımlı veya tamamen parent lifecycle'ına bağlı kayıtlar için cascade son derece temiz bir çözüm olabilir. Ama:

Audit
History
Transaction
Payment
Usage
Security Event

gibi kayıtların bulunduğu ilişkilerde aynı rahatlıkla kullanmak ciddi risk yaratabilir.

Sonuç

ON DELETE CASCADE, SQL Server'daki en basit görünen ama etkisi en kolay gözden kaçabilen foreign key seçeneklerinden biri. Bir parent kaydı sildiğinizde ona bağlı kayıtları otomatik temizlemesi kodu sadeleştirebilir ve veri bütünlüğünü koruyabilir. Ama aynı mekanizma karmaşık bir ilişki ağında tek bir DELETE sorgusunun binlerce kaydı silmesine de neden olabilir. Bu yüzden benim yaklaşımım şu: Bir foreign key'e cascade eklemeden önce "silme işlemi çalışıyor mu?" sorusundan çok "bu kayıt silindiğinde gerçekten bağlı her şeyin de yok olmasını istiyor muyum?" sorusunu sormak. Çünkü bazen SQL Server'ın verdiği foreign key hatası çözülmesi gereken bir problem değil, veritabanının sizi durdurmak için verdiği oldukça değerli bir uyarıdır.