SQLite Foreign Key (Yabancı Anahtar) Rehberi

Terimler: üst tablo, alt tablo, anahtarlar

SQLite belgeleri dört terim kullanır. Kısıtın işaret ettiği tablo üst tablo (parent table), REFERENCES ifadesini taşıyan tablo alt tablo (child table) olarak adlandırılır. Üst tablodaki hedef sütunlar üst anahtar, alt tablodaki sütunlar alt anahtar olur. Kısıt şu durumda sağlanmış sayılır: alt tablodaki her satır için ya alt anahtar sütunlarından en az biri NULL'dur ya da üst tabloda değerleri birebir eşleşen bir satır vardır. Karşılaştırmada üst anahtar sütununun sıralama kuralı (collation) ve tip eğilimi kullanılır.

Üst anahtar genellikle üst tablonun birincil anahtarıdır. Değilse, sütunların birlikte bir UNIQUE kısıtına ya da benzersiz bir indekse sahip olması gerekir. Rowid'e doğrudan başvurulamaz; üst anahtar mutlaka adı olan bir sütun olmalıdır. Bu yüzden üst tabloda INTEGER PRIMARY KEY tanımlamak en yaygın çözümdür; nedenleri PRIMARY KEY ve rowid yazısında.

Denetim kapalıyken ne olur

Aşağıdaki örnekte tabloları ve kısıtı tanımladık ama ayarı açmadık. Var olmayan 99 numaralı yazara işaret eden kitap sorunsuz eklendi. PRAGMA foreign_key_check ise ayar kapalıyken bile ihlalleri listeler; her satırda alt tablonun adı, ihlal eden satırın rowid'i, üst tablonun adı ve kısıtın sıra numarası bulunur:

PRAGMA foreign_keys;
-- çıktı: 0

CREATE TABLE yazar(id INTEGER PRIMARY KEY, ad TEXT NOT NULL);
CREATE TABLE kitap(
  id       INTEGER PRIMARY KEY,
  baslik   TEXT NOT NULL,
  yazar_id INTEGER REFERENCES yazar(id)
);
INSERT INTO yazar VALUES (1, 'Sait Faik'), (2, 'Sabahattin Ali');
INSERT INTO kitap VALUES (10, 'Semaver', 1);
INSERT INTO kitap VALUES (11, 'Hayalet kitap', 99);   -- denetim kapalı: kabul edildi

SELECT id, baslik, yazar_id FROM kitap;
-- çıktı:
-- 10|Semaver|1
-- 11|Hayalet kitap|99

PRAGMA foreign_key_check;
-- çıktı: kitap|11|yazar|0

Belgelere göre bu varsayılan, geriye dönük uyumluluk için seçilmiştir ve ileride değişebilir. Derleme sırasında SQLITE_DEFAULT_FOREIGN_KEYS ile varsayılan değiştirilebildiği için bazı derlemelerde ayar açık gelebilir. Bu yüzden belgelerin önerisi, varsayılana güvenmeden ayarı her bağlantıda açıkça yapmanızdır.

Kısıtı etkinleştirmek

Ayar veritabanı dosyasına değil bağlantıya aittir. Komut satırında, uygulamanızda veya bir yönetim aracında her yeni bağlantı için ayrıca açılmalıdır. Ayar açıldıktan sonra üç tür işlem denetlenir: alt tabloya ekleme ve güncelleme, üst tablodan silme ve üst anahtarın değiştirilmesi.

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
-- çıktı: 1

INSERT INTO kitap VALUES (12, 'Yine hayalet', 98);
-- hata: FOREIGN KEY constraint failed
INSERT INTO kitap VALUES (13, 'Yazarı belirsiz', NULL);   -- NULL serbest
DELETE FROM yazar WHERE id = 1;
-- hata: FOREIGN KEY constraint failed
UPDATE yazar SET id = 5 WHERE id = 1;
-- hata: FOREIGN KEY constraint failed

DELETE FROM kitap WHERE id = 11;      -- eski bozuk satırı temizleyelim
PRAGMA foreign_key_check;             -- artık satır dönmez

Hata mesajı hangi kısıtın ihlal edildiğini söylemez; yalnızca "FOREIGN KEY constraint failed" yazar. Birden fazla yabancı anahtarı olan tablolarda hangisinin sorun çıkardığını bulmak için pragma_foreign_key_list ile kısıtları listeleyip ilgili üst tabloları kontrol edebilirsiniz. Diğer hata kodları için SQLite hata kodları yazısına bakın.

SELECT * FROM pragma_foreign_key_list('kitap');
-- çıktı: 0|0|yazar|yazar_id|id|NO ACTION|NO ACTION|NONE

BEGIN;
PRAGMA foreign_keys = OFF;            -- işlem içinde etkisiz
PRAGMA foreign_keys;
-- çıktı: 1
COMMIT;

Programlama dillerinden SQLite kullanırken ayarı bağlantıyı açar açmaz çalıştırmanız gerekir; örnekler için Python sqlite3, Node.js sqlite modülü ve PHP PDO yazılarına bakabilirsiniz.

ON DELETE ve ON UPDATE eylemleri

Bir yabancı anahtar, üst satır silindiğinde (ON DELETE) ve üst anahtar değiştirildiğinde (ON UPDATE) ne olacağını ayrı ayrı tanımlayabilir. Eylem belirtilmezse NO ACTION geçerlidir.

Aşağıdaki örnekte siparişler müşteriyle birlikte silinip güncelleniyor, yorumlar ise müşteri silindiğinde sahipsiz kalıyor. Yorum tablosuna da ON UPDATE CASCADE ekledik; eklemeseydik müşteri kimliğini değiştirmek, yorumlardaki eski değer yüzünden reddedilirdi:

CREATE TABLE musteri(id INTEGER PRIMARY KEY, ad TEXT);
CREATE TABLE siparis(
  id INTEGER PRIMARY KEY,
  musteri_id INTEGER REFERENCES musteri(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE yorum(
  id INTEGER PRIMARY KEY,
  musteri_id INTEGER REFERENCES musteri(id) ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE INDEX siparis_musteri ON siparis(musteri_id);
CREATE INDEX yorum_musteri ON yorum(musteri_id);

INSERT INTO musteri VALUES (1, 'Ada'), (2, 'Bora');
INSERT INTO siparis VALUES (100, 1), (101, 1), (102, 2);
INSERT INTO yorum VALUES (500, 1), (501, 2);

UPDATE musteri SET id = 10 WHERE id = 1;
SELECT id, musteri_id FROM siparis;
-- çıktı:
-- 100|10
-- 101|10
-- 102|2

DELETE FROM musteri WHERE id = 10;
SELECT id, musteri_id FROM siparis;
-- çıktı: 102|2
SELECT id, quote(musteri_id) FROM yorum;
-- çıktı:
-- 500|NULL
-- 501|2

SET DEFAULT kısıtı ortadan kaldırmaz

Bir eylem tanımlamak, kısıtın sağlanması gerekliliğini kaldırmaz. SET DEFAULT alt anahtarı varsayılan değere çeker; o değere karşılık gelen bir üst satır yoksa silme yine başarısız olur. Belgelerdeki örneğin benzerinde varsayılan 0 değerine karşılık gelen bir kayıt ekledikten sonra silme gerçekleşiyor:

CREATE TABLE bayi(id INTEGER PRIMARY KEY, ad TEXT);
CREATE TABLE fatura(
  id INTEGER PRIMARY KEY,
  bayi_id INTEGER DEFAULT 0 REFERENCES bayi(id) ON DELETE SET DEFAULT
);
INSERT INTO bayi VALUES (2, 'Kuzey');
INSERT INTO fatura VALUES (900, 2);

DELETE FROM bayi WHERE id = 2;        -- 0 numaralı bayi yok
-- hata: FOREIGN KEY constraint failed

INSERT INTO bayi VALUES (0, 'Atanmamış');
DELETE FROM bayi WHERE id = 2;
SELECT id, bayi_id FROM fatura;
-- çıktı: 900|0

ON UPDATE yalnızca değer değişirse çalışır

Yabancı anahtar eylemleri tetikleyicilere benzer, ama önemli bir farkla: ON UPDATE eylemi yalnızca üst anahtarın değeri gerçekten değiştiğinde uygulanır. Aynı değeri yeniden yazmak eylemi tetiklemez. Tetikleyicilerle ilgili ayrıntılar trigger yazısında.

CREATE TABLE ebeveyn(x PRIMARY KEY);
CREATE TABLE cocuk(y REFERENCES ebeveyn ON UPDATE SET NULL);
INSERT INTO ebeveyn VALUES ('anahtar');
INSERT INTO cocuk VALUES ('anahtar');

UPDATE ebeveyn SET x = 'anahtar';     -- değer aynı kaldı
SELECT quote(y) FROM cocuk;
-- çıktı: 'anahtar'
UPDATE ebeveyn SET x = 'yeni';        -- değer değişti
SELECT quote(y) FROM cocuk;
-- çıktı: NULL

Belgeler bir üst satır silindiğinde veya anahtarı değiştiğinde olayların mantıksal sırasını da verir: önce BEFORE tetikleyicileri çalışır, sonra yabancı anahtar dışındaki yerel kısıtlar denetlenir, satır güncellenir veya silinir, gerekli yabancı anahtar eylemleri uygulanır ve en son AFTER tetikleyicileri çalışır. Yabancı anahtar eylemleri tetikleyici derinlik sınırına tabidir ve PRAGMA recursive_triggers ayarından etkilenmez.

Ertelenmiş kısıtlar

Yabancı anahtar kısıtları varsayılan olarak anlıktır: ihlal, ifade bittiğinde denetlenir ve ifade geri alınır. DEFERRABLE INITIALLY DEFERRED ile tanımlanan bir kısıt ise işlem COMMIT edilene kadar denetlenmez. Bu, birbirine bağlı satırları sırası önemli olmadan eklemenizi sağlar. COMMIT ihlal varken başarısız olur ve işlem açık kalır; eksik satırı ekleyip yeniden deneyebilirsiniz:

CREATE TABLE kategori(id INTEGER PRIMARY KEY, ad TEXT);
CREATE TABLE urun(
  id INTEGER PRIMARY KEY,
  kategori_id INTEGER REFERENCES kategori(id) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO urun VALUES (1, 7);       -- henüz hata yok
COMMIT;
-- hata: FOREIGN KEY constraint failed
INSERT INTO kategori VALUES (7, 'Kırtasiye');   -- işlem hâlâ açık
COMMIT;

SELECT u.id, k.ad FROM urun u JOIN kategori k ON k.id = u.kategori_id;
-- çıktı: 1|Kırtasiye

INSERT INTO urun VALUES (2, 8);       -- işlem dışında: hemen denetlenir
-- hata: FOREIGN KEY constraint failed

Açık bir işlem yokken her ifade kendi örtük işleminde çalıştığı için ertelenmiş kısıt da anlık gibi davranır. NOT DEFERRABLE, DEFERRABLE INITIALLY IMMEDIATE veya yalnızca DEFERRABLE yazmak anlık kısıt üretir. SQLite, bir kısıtı sonradan ertelenmiş ve anlık arasında değiştiren SET CONSTRAINT benzeri bir komutu desteklemez; kısıtın türü oluşturulurken belirlenir.

defer_foreign_keys ile geçici erteleme

Kısıtları tek bir işlem için ertelemek istiyorsanız PRAGMA defer_foreign_keys = ON kullanabilirsiniz. Bu ayar, tanımlarından bağımsız olarak bütün yabancı anahtarları en dıştaki işlem onaylanana kadar erteler ve her COMMIT veya ROLLBACK sonrasında kendiliğinden kapanır. Toplu veri aktarımında sıralama derdinden kurtulmak için kullanışlıdır.

CREATE TABLE etiket(id INTEGER PRIMARY KEY);
CREATE TABLE baglanti(etiket_id INTEGER REFERENCES etiket(id));

BEGIN;
PRAGMA defer_foreign_keys = ON;
INSERT INTO baglanti VALUES (3);      -- çocuk önce
INSERT INTO etiket VALUES (3);        -- ebeveyn sonra
COMMIT;

SELECT count(*) FROM baglanti;
-- çıktı: 1
PRAGMA defer_foreign_keys;            -- COMMIT ile kendiliğinden kapandı
-- çıktı: 0

RESTRICT ile NO ACTION farkı

Bu fark en çok ertelenmiş kısıtlarda görünür. NO ACTION denetimi ifadenin ya da işlemin sonuna bırakır. RESTRICT ise alan değiştiği anda devreye girer; kısıt ertelenmiş olsa bile bağlı alt satırı olan bir üst satırı silmenize izin vermez:

CREATE TABLE r_ebeveyn(id INTEGER PRIMARY KEY);
CREATE TABLE r_cocuk(
  eid INTEGER REFERENCES r_ebeveyn(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO r_ebeveyn VALUES (1);
INSERT INTO r_cocuk VALUES (1);

BEGIN;
DELETE FROM r_ebeveyn WHERE id = 1;   -- ertelenmiş olsa da RESTRICT hemen durdurur
-- hata: FOREIGN KEY constraint failed
ROLLBACK;

İndeksler: zorunlu olan ve önerilen

Üst anahtar sütunlarının birincil anahtar ya da tam olarak bu sütunları kapsayan bir benzersiz indeks olması zorunludur ve indeksin sıralama kuralı tablo tanımındakiyle aynı olmalıdır. Bu şart sağlanmazsa hata tablo oluşturulurken değil, alt veya üst tabloyu değiştiren ifade hazırlanırken ortaya çıkar. Belgeler bunlara "DML hatası" der; mesaj genellikle "foreign key mismatch", üst tablo yoksa "no such table" olur:

CREATE TABLE ana(a PRIMARY KEY, b UNIQUE, e);
CREATE TABLE c1(x REFERENCES ana(b));     -- geçerli: b benzersiz
CREATE TABLE c2(x REFERENCES ana(e));     -- tablo oluşur, ama...
INSERT INTO c2 VALUES (1);
-- hata: foreign key mismatch - "c2" referencing "ana"

CREATE TABLE c3(x REFERENCES olmayan(id));
INSERT INTO c3 VALUES (1);
-- hata: no such table: main.olmayan

Alt anahtar sütunları için indeks zorunlu değildir, ama belgeler gerçek sistemlerin çoğunda bunun gerektiğini vurgular. Üst tablodan her silmede veya anahtar değişikliğinde SQLite, alt tabloda bu değere işaret eden satır olup olmadığını arar. İndeks yoksa bu arama bütün alt tabloyu tarar. Sorgu planında farkı görebilirsiniz:

CREATE TABLE p(id INTEGER PRIMARY KEY);
CREATE TABLE ch(id INTEGER PRIMARY KEY);
INSERT INTO p VALUES (1);
INSERT INTO ch VALUES (5);

ALTER TABLE ch ADD COLUMN pid2 INTEGER DEFAULT 1 REFERENCES p(id);
-- hata: Cannot add a REFERENCES column with non-NULL default value
ALTER TABLE ch ADD COLUMN pid3 INTEGER REFERENCES p(id);

EXPLAIN QUERY PLAN DELETE FROM p WHERE id = 1;
-- çıktı:
-- QUERY PLAN
-- |--SEARCH p USING INTEGER PRIMARY KEY (rowid=?)
-- `--SCAN ch

CREATE INDEX ch_pid3 ON ch(pid3);
EXPLAIN QUERY PLAN DELETE FROM p WHERE id = 1;
-- çıktı:
-- QUERY PLAN
-- |--SEARCH p USING INTEGER PRIMARY KEY (rowid=?)
-- `--SEARCH ch USING COVERING INDEX ch_pid3 (pid3=?)

İlk planda alt tablo baştan sona taranıyor (SCAN ch); indeksten sonra arama doğrudan indeksle yapılıyor. Alt anahtar indeksinin benzersiz olması gerekmez ve çoğu zaman da olmaz. Plan okuma için EXPLAIN QUERY PLAN, indeks tasarımı için index oluşturma yazılarına bakın.

CREATE, ALTER ve DROP TABLE ile etkileşim

CREATE TABLE ayar açık olsun kapalı olsun aynı çalışır ve üst tabloyu denetlemez; var olmayan bir tabloya işaret eden kısıt tanımlamanızı engellemez. Ayar açıkken iki komut farklı davranır. Birincisi, ALTER TABLE ... ADD COLUMN ile REFERENCES içeren bir sütun eklerken varsayılan değer NULL olmalıdır; yukarıdaki örnekte DEFAULT 1 verilen sütun reddedildi. Denememizde bu hata, tabloda satır varken alındı. İkincisi, üst tablo RENAME TO ile yeniden adlandırıldığında alt tablolardaki tanımlar yeni adı gösterecek biçimde güncellenir. Ayrıntılar ALTER TABLE yazısında.

Ayar açıkken DROP TABLE tabloyu kaldırmadan önce bütün satırlarını silen örtük bir DELETE yapar. Bu silme tetikleyicileri çalıştırmaz ama yabancı anahtar eylemlerini ve ihlallerini tetikleyebilir. Anlık bir kısıt ihlal edilirse tablo kaldırılmaz:

CREATE TABLE oda(id INTEGER PRIMARY KEY);
CREATE TABLE rezervasyon(oda_id INTEGER REFERENCES oda(id));
INSERT INTO oda VALUES (1);
INSERT INTO rezervasyon VALUES (1);

DROP TABLE oda;
-- hata: FOREIGN KEY constraint failed
DROP TABLE rezervasyon;
DROP TABLE oda;

Var olan veride ihlal aramak

Kısıtları sonradan açtığınız ya da veriyi ayar kapalıyken içeri aldığınız durumlarda, PRAGMA foreign_key_check ile bütün veritabanını veya tek bir tabloyu tarayabilirsiniz. Şema değişikliği yapan on iki adımlı yöntem de bu komutla biter. Bu komutu bakım rutininizin bir parçası yapmak, ayarın bir yerde unutulduğu bağlantıların bıraktığı bozuklukları erken yakalar. Bütünlük denetiminin diğer araçları bozuk veritabanı kurtarma yazısında anlatılıyor.

Desteklenmeyen özellikler ve sınırlar

• MATCH ifadesi uygulanmaz. SQLite MATCH FULL gibi ifadeleri ayrıştırır ama yok sayar; bütün kısıtlar MATCH SIMPLE gibi işlenir, yani alt anahtar sütunlarından biri NULL ise üst satır aranmaz.
• Kısıt türü sonradan değiştirilemez. Ertelenmiş veya anlık olma özelliği oluşturma anında sabitlenir.
• Şema sınırı aşılamaz. Bir yabancı anahtar yalnızca kendi bulunduğu veritabanındaki tablolara başvurabilir; ATTACH ile bağlanan başka bir dosyadaki tabloya işaret edemez. Bkz. ATTACH DATABASE.
• Derleme seçenekleri: SQLITE_OMIT_FOREIGN_KEY veya SQLITE_OMIT_TRIGGER ile derlenen kütüphanelerde kısıtlar uygulanmaz. PRAGMA foreign_keys hiç satır döndürmüyorsa kullandığınız derleme yabancı anahtarları desteklemiyor demektir.

Sık yapılan hatalar

• Ayarı açmayı unutmak: Kısıtlar tanımlı ama hiçbir şey denetlenmiyor; her bağlantıda PRAGMA foreign_keys = ON çalıştırın.
• Ayarı işlem içinde değiştirmek: Hata vermeden etkisiz kalır.
• Alt anahtara indeks koymamak: Üst tablodaki her silme alt tabloyu baştan sona tarar.
• Üst anahtarı benzersiz yapmamak: Tablo oluşur ama ilk yazmada "foreign key mismatch" alırsınız.
• NOT NULL eklememek: NULL alt anahtar her zaman kısıtı sağlar; ilişki zorunluysa sütuna NOT NULL ekleyin.

Sık Sorulan Sorular

PRAGMA foreign_keys ayarı veritabanında kalıcı mı?

Hayır. Ayar bağlantıya özeldir ve her yeni bağlantıda yeniden yapılmalıdır. Kalıcı ve bağlantıya özel ayarların listesi için PRAGMA komutları yazısına bakın.

Toplu veri yüklerken yabancı anahtarları nasıl yönetmeliyim?

İki yol var: veriyi üst tablolardan başlayarak doğru sırada yüklemek ya da işlemi başlatıp PRAGMA defer_foreign_keys = ON ile denetimi işlem sonuna ertelemek. İkinci yolda ihlal kalırsa COMMIT başarısız olur ve hiçbir şey kaybolmaz.

Birden fazla sütunlu yabancı anahtar tanımlayabilir miyim?

Evet. FOREIGN KEY(a, b) REFERENCES ust(x, y) biçiminde bileşik kısıtlar desteklenir. Alt ve üst anahtarın sütun sayısı aynı olmalı ve üst sütunlar birlikte benzersiz olmalıdır. Alt sütunlardan biri NULL ise üst satır aranmaz.

CASCADE ile silinen satırlar tetikleyicileri çalıştırır mı?

Yabancı anahtar eylemleri belgelerde tetikleyici programları gibi ele alınır ve olay sırası yukarıda verildi. Silme ve güncelleme tetikleyicileriyle birlikte kullanıyorsanız sırayı küçük bir deneme veritabanında doğrulayın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · PRIMARY KEY ve rowid · Index oluşturma · Transaction ve SAVEPOINT · Trigger tetikleyiciler · ALTER TABLE