SQLite Foreign Key: REFERENCES, ON DELETE ve PRAGMA

Foreign Key, Tablolar Arasındaki Bir İşaretçidir

SQLite foreign key, bir tablodaki değeri başka bir tablodaki satırla eşleşmek zorunda olan bir sütundur. İlişkisel veritabanları "şu posts satırı, şu authors satırına ait" demeyi işte böyle ifade eder; yazarın adını ve e-postasını her gönderiye kopyalamak zorunda kalmadan.

Aşağıda olabilecek en sade örnek var: bir parent tablo ile bir child tablo, FK üzerinden birbirine bağlı:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Merhaba', 1), ('Notlar', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) ifadesi, foreign key tanımının tamamıdır. Şunu söyler: bu kolon, authors tablosundaki bir id değerini tutar. Veritabanı artık iki tablonun ilişkili olduğunu bilir ve — eğer kısıtlama denetimi açıksa — var olmayan bir yazara işaret eden insert'leri reddeder.

SQLite Foreign Key Varsayılan Olarak Kapalıdır

SQLite foreign key konusunda bilmeniz gereken en kritik bilgi bu ve herkesi şaşırtıyor: SQLite, REFERENCES ifadelerini ayrıştırır ama sen açıkça istemediğiniz sürece bunları zorlamaz. Sebebi tarihsel uyumluluk — eski veritabanları, bu özellik var olmadan önce kurulmuştu.

Kısıtlama denetimi kapalıyken neler olduğuna bakalım:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- 999 numaralı yazar mevcut değil, ancak SQLite bunu sorunsuzca kabul eder:
INSERT INTO posts (title, author_id) VALUES ('Yetim', 999);

SELECT * FROM posts;

Sahipsiz satır hiçbir uyarı vermeden geçti. Aslında istediğiniz korumayı elde etmek için her bağlantının başında PRAGMA foreign_keys = ON; komutunu çalıştırmanız gerekiyor:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Yetim', 999);

Artık INSERT hata veriyor: FOREIGN KEY constraint failed. Bu pragma bağlantı bazında çalışır, veritabanı bazında değil — yani ayar dosyaya kaydedilmiyor. Her uygulama, her CLI oturumu, her test fixture'ı bu ayarı kendisi yapmak zorunda. Üretim kodunun çoğu, bağlantı açılır açılmaz PRAGMA foreign_keys = ON; komutunu çalıştırır.

REFERENCES İfadesi Aslında Ne Bekliyor?

Referans verdiğiniz kolonun ya PRIMARY KEY olması ya da UNIQUE kısıtına sahip olması şart. SQLite ancak bu sayede aramanın tek bir kayda denk geldiğini garanti edebiliyor. Tipler de uyumlu olmalı — SQLite tip konusunda esnek davransa da, farklı tipleri karıştırmak ileride başınıza iş açar.

SQLite foreign key tanımını iki şekilde yazabilirsiniz. İlki, kolonun yanında satır içi olarak:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Ya da birden fazla sütunu kapsayan foreign key tanımlamak istediğinizde zorunlu olduğu gibi, ayrı bir tablo seviyesinde kısıtlama olarak da yazabilirsiniz:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Her iki kullanım da aynı kısıtı üretir. Tablonuza hangisi daha okunaklı geliyorsa onu tercih edin.

ON DELETE: Çocuk Kayıtların Akıbeti

Bir parent kaydı sildiğinizde, ona bağlı child kayıtlarına ne olacağına SQLite'ın karar vermesi gerekir. Bu davranışı ON DELETE ile siz belirlersiniz:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Ada'yı silmek, ona ait iki gönderiyi de sildi. Seçenekler şöyle:

• CASCADE — alt kayıtları da siler. Bir yazara ait gönderiler veya bir siparişe ait kalemler gibi "sahipli" veriler için ideal.
• SET NULL — FK kolonunu NULL yapar. Alt kayıtlar üst kayıt olmadan da yaşamalıysa uygundur (mesela silinen bir kullanıcının yorumları anonim hale gelsin).
• SET DEFAULT — FK kolonunu tanımlanmış varsayılan değerine çeker.
• RESTRICT — alt kayıt varsa silmeyi engeller. İşlem sırasında anında hata verir.
• NO ACTION — varsayılan davranış. Çoğu durumda RESTRICT ile aynı işi görür (kontrolü commit anına erteler ama sonuç değişmez: ortada sahipsiz alt kayıt bırakamazsınız).

ON UPDATE ise üst tablonun anahtarı değiştiğinde aynı mantıkla çalışır — gerçi primary key güncellemek pek karşılaşılan bir şey değildir.

Foreign Key Constraint Failed Hatası Ne Anlama Geliyor?

Bu hatayla genelde iki durumda karşılaşırsınız. İlki, bir alt kayda eşleşen üst kaydı olmayan bir değer eklemeye veya güncellemeye çalıştığınızda:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Ya 999 numaralı yazar tabloda yok ya da sütun tiplerini karıştırmışsınız. Önce parent kaydı ekle, ya da değeri düzelt.

İkincisi, FK RESTRICT veya NO ACTION ile tanımlıyken hâlâ child kaydı olan bir parent'ı silmeye (veya güncellemeye) çalışmak:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Ya önce alt kayıtları silersiniz, ya da gerçekten kademeli (cascade) bir davranış istiyorsanız foreign key'i ON DELETE CASCADE veya SET NULL olarak değiştirirsiniz.

Bunun bir de daha az karşılaşılan akrabası var: FOREIGN KEY mismatch. Bu hata, referans verilen sütun primary key ya da unique değilse veya sütun sayıları birbirini tutmuyorsa karşımıza çıkar. Yani bu bir veri hatası değil, şema hatasıdır.

Var Olan Tablolara Foreign Key Ekleme

SQLite'ta ALTER TABLE oldukça kısıtlıdır — yeni bir sütunu foreign key ile birlikte ekleyebilirsiniz, ama mevcut bir sütuna sonradan foreign key tanımlayamazsınız. Bu durumda standart çözüm, "yeniden adlandır ve yeniden oluştur" yöntemidir:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Merhaba', 1), ('Dünya', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Bu desen şöyle işliyor: önce kısıtlama denetimini kapatıyorsunuz, istediğiniz constraint'lerle yeni tabloyu oluşturuyorsunuz, veriyi kopyalıyorsunuz, eski tabloyu siliyor ve yenisini onun adıyla yeniden adlandırıyorsunuz. BEGIN/COMMIT sayesinde tüm bu işlem atomik kalıyor. En sonda denetimi tekrar açtığında SQLite, mevcut tüm satırları yeni kısıtlamalara göre doğrulayacaktır — ama dikkat: veri geçersiz olsa bile transaction çoktan commit edilmiş olur. Bu yüzden tedirginsen önceden kontrol et.

Migration sonrası PRAGMA foreign_key_check; çalıştırarak yetim (orphan) satır kalmadığından emin ol.

Gerçekçi Bir Şema Örneği

Şimdi tüm parçaları birleştirelim — ebeveyn-çocuk ilişkileri ve çoka-çok etiketleme için bir join tablosu içeren minik bir blog şeması:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Dikkat etmeniz gereken üç şey var. author_id alanı NOT NULL olarak tanımlı, yani her gönderinin mutlaka bir yazarı olmak zorunda. posts → authors foreign key'i CASCADE ile çalıştığı için bir yazarı sildiğinizde o yazara ait tüm gönderiler de silinir. post_tags ara tablosu ise her iki taraftan da CASCADE yapıyor; yani bir gönderiyi ya da bir etiketi sildiğinizde, ilişki satırları otomatik olarak temizlenir.

İleride Başınızı Ağrıtmayacak Alışkanlıklar

• Her bağlantıda PRAGMA foreign_keys = ON; komutunu çalıştırın. Bunu aklınızda tutmaya çalışmak yerine, uygulamanızın veritabanı açma rutininin bir parçası haline getirin.
• FK sütununa bir index ekleyin. SQLite parent tarafındaki anahtarı otomatik olarak indeksler ama child tarafını indekslemez. Üstelik ON DELETE CASCADE, parent her silindiğinde child üzerinde arama yapar.
• ON DELETE davranışını bilinçli seçin. Varsayılan değer (NO ACTION) güvenlidir ama bu, her temizlik denemenizde "constraint failed" hatasıyla karşılaşacağınız anlamına gelir. Ne olmasını istiyorsanız onu açıkça belirtin.
• Migration veya toplu veri aktarımlarından sonra PRAGMA foreign_key_check; çalıştırarak yetim (orphan) kayıtları, hataya dönüşmeden önce yakalayın.

Sıradaki Konu: INNER JOIN

Foreign key'ler ilişkinin nasıl olduğunu tanımlar; join'ler ise bu ilişki üzerinden gerçekten sorgu yapmanın yoludur. Bir sonraki sayfada INNER JOIN konusuna geçeceğiz: ilişkili tabloların satırlarını birleştirip her tablodan istediğiniz sütunları nasıl alacağınızı göreceğiz.