Primary Key ve Foreign Key Tasarımı

Primary key ve foreign key, şema tasarımının iki taşıyıcı kolonudur ama çoğu projede üzerinde en az düşünülen kararlardır. Her tabloya refleks olarak id INT AUTO_INCREMENT konur, yabancı anahtar kısıtları "uygulama zaten kontrol ediyor" gerekçesiyle hiç tanımlanmaz ve aylar sonra öksüz satırlarla, tekrarlı kayıtlarla ve açıklanamayan raporlarla uğraşılır. Oysa bu iki kısıt yalnızca kural koymaz; InnoDB'de verinin diskte nasıl dizildiğini bile belirler.

Bu rehberde birincil anahtarın fiziksel olarak ne yaptığını, doğal ve yapay anahtar arasında nasıl seçim yapacağınızı, yabancı anahtar kısıtlarının hangi referans eylemleriyle çalıştığını ve errno 150, 1451, 1452 gibi klasik hataların gerçek sebeplerini anlatacağım. Örnekler MySQL/InnoDB üzerinden ilerleyecek, PostgreSQL'in farklı davrandığı noktaları ayrıca belirteceğim.

Primary Key Ne Yapar, Neden Her Tabloda Olmalı#

Birincil anahtar üç işi aynı anda yapar: satırı benzersiz biçimde tanımlar, NULL değere izin vermez ve tablo üzerinde bir indeks oluşturur. Bu üçüncü madde InnoDB'de sıradan bir indeks değildir; birincil anahtar aynı zamanda kümelenmiş indekstir (clustered index), yani satırların diskteki fiziksel sıralamasını belirler. Satır verisi, birincil anahtar ağacının yapraklarında saklanır.

Bunun iki doğrudan sonucu vardır. Birincisi, birincil anahtara göre yapılan aramalar en hızlı erişim yoludur çünkü indeksi bulduğunuz anda satırın kendisine ulaşmışsınızdır. İkincisi, tüm ikincil indeksler yaprak düğümlerinde birincil anahtarın kopyasını taşır; yani birincil anahtarınız ne kadar genişse her ikincil indeks o kadar şişer.

Tabloya hiç birincil anahtar koymazsanız InnoDB pes etmez: önce NOT NULL tanımlı bir UNIQUE indeks arar, onu da bulamazsa satır başına 6 baytlık gizli bir dahili kimlik üretir. Bu gizli anahtarı siz sorgulayamaz, indeksleyemez ve replikasyonda kullanamazsınız; üstelik yer kaplamaya devam eder. Yani anahtarsız tablo "hafif" değil, sadece kontrolsüzdür.

-- Birincil anahtarı olmayan tabloları bulun (MySQL 8)
SELECT t.table_schema, t.table_name
FROM   information_schema.tables t
LEFT   JOIN information_schema.table_constraints c
       ON  c.table_schema = t.table_schema
       AND c.table_name   = t.table_name
       AND c.constraint_type = 'PRIMARY KEY'
WHERE  t.table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
  AND  t.table_type = 'BASE TABLE'
  AND  c.constraint_name IS NULL;

Bu sorgunun boş dönmesi, sağlıklı bir şemanın ilk göstergelerinden biridir. Anahtarsız büyük tablolar ayrıca satır tabanlı replikasyonda ciddi yavaşlığa yol açar, çünkü kopya sunucu güncellenecek satırı bulmak için tam tarama yapmak zorunda kalır.

Doğal Anahtar mı Yapay Anahtar mı#

Doğal anahtar, verinin kendisinden gelen benzersiz bir değerdir: plaka kodu, ISBN, vergi numarası, ISO ülke kodu. Yapay (surrogate) anahtar ise anlamı olmayan, sırf tanımlamak için üretilmiş bir sayıdır: AUTO_INCREMENT ile artan bir id.

Pratikte izlenmesi kolay bir kural şudur: birincil anahtar olarak dar ve değişmez bir yapay anahtar kullanın, doğal anahtarı UNIQUE kısıtıyla koruyun. Böylece hem birleştirmeler ucuz kalır hem de iş kuralı ihlali veritabanı düzeyinde engellenir.

CREATE TABLE sehirler (
  id         SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  plaka      CHAR(2) NOT NULL,          -- doğal anahtar
  sehir_adi  VARCHAR(60) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_plaka (plaka),          -- benzersizlik yine garanti altında
  UNIQUE KEY uq_sehir_adi (sehir_adi)
) ENGINE=InnoDB;

Doğal anahtarın birincil anahtar olduğu meşru durumlar da vardır: değerin gerçekten hiç değişmediği, dar olduğu ve zaten her sorguda kullanıldığı küçük referans tabloları. Ama e-posta adresini birincil anahtar yapmak neredeyse her zaman hatadır; kullanıcı e-postasını değiştirdiğinde ona bağlı tüm yabancı anahtarların da güncellenmesi gerekir. Anahtar tipini sayı yerine rastgele bir tanımlayıcı yapmayı düşünüyorsanız UUID mi auto increment mi karşılaştırması bu kararın performans tarafını ayrıntılı ele alıyor.

Birincil Anahtarın Fiziksel Etkisi#

Kümelenmiş indeks yapısı, birincil anahtar seçiminde iki ölçütü öne çıkarır: darlık ve artan sıra.

Darlık, ikincil indekslerin boyutunu doğrudan belirler. Beş ikincil indeksi olan 20 milyon satırlık bir tabloda birincil anahtarı 4 baytlık INT yerine 16 baytlık bir ikili değere çevirirseniz, sadece indeks kopyaları için satır başına 60 bayt fazladan yük gelir; bu da yaklaşık 1,2 GB ek indeks demektir.

Artan sıra ise ekleme performansını belirler. AUTO_INCREMENT değerler sürekli büyüdüğü için her yeni satır B-ağacının sağ ucuna eklenir; bu, sayfa bölünmesi (page split) neredeyse hiç yaşanmaması anlamına gelir. Rastgele bir anahtarla ekleme yaparsanız, satır ağacın ortasına düşer, sayfa dolu ise ikiye bölünür ve zamanla tablo parçalanır (fragmentation).

-- İyi: dar ve artan
PRIMARY KEY (id)                       -- BIGINT UNSIGNED AUTO_INCREMENT

-- Dikkatli olun: geniş, metin tabanlı ve rastgele
PRIMARY KEY (uuid_metin)               -- CHAR(36), her ikincil indekste 36 bayt

-- Ara tabloda doğru kullanım: dar, bileşik, doğal
PRIMARY KEY (siparis_no, urun_id)

Tablonun ve indekslerinin gerçek boyutunu görmek isterseniz:

SELECT table_name,
       ROUND(data_length/1024/1024, 1)  AS veri_mb,
       ROUND(index_length/1024/1024, 1) AS indeks_mb
FROM   information_schema.tables
WHERE  table_schema = DATABASE()
ORDER  BY index_length DESC;

İndeks boyutu veri boyutunu geçmişse, muhtemelen ya çok fazla indeksiniz var ya da birincil anahtarınız gereğinden geniştir.

Foreign Key: Kısıtlar ve Referans Eylemleri#

Yabancı anahtar, bir tablodaki değerin başka bir tabloda gerçekten var olmasını garanti eder. "Uygulama zaten kontrol ediyor" savunması pratikte hiç tutmaz, çünkü uygulama tek yazma yolu değildir: toplu içe aktarmalar, bakım betikleri, elle çalıştırılan SQL komutları ve ikinci bir servis aynı tabloya yazar. Kısıt veritabanındaysa hepsi aynı kurala uyar.

CREATE TABLE siparisler (
  siparis_no  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  musteri_id  INT UNSIGNED NOT NULL,
  PRIMARY KEY (siparis_no),
  KEY idx_musteri (musteri_id),
  CONSTRAINT fk_siparis_musteri
    FOREIGN KEY (musteri_id) REFERENCES musteriler(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

Referans eylemleri, ana kayıt silindiğinde veya güncellendiğinde ne olacağını belirler:

ON DELETE CASCADE güçlü ama tehlikeli bir araçtır. Bir müşteriyi sildiğinizde siparişlerinin, sipariş kalemlerinin ve faturalarının zincirleme silinmesi genellikle istediğiniz şey değildir. Kural olarak CASCADE'i yalnızca gerçek bir "parça-bütün" ilişkisinde kullanın: sipariş silindiğinde kalemleri anlamsızlaşır, evet; müşteri silindiğinde faturaları anlamsızlaşmaz, hayır. Finansal ve yasal kayıtlarda silme yerine yumuşak silme (silindi_mi sütunu) çoğu zaman daha doğrudur.

ON UPDATE CASCADE ise doğal anahtar kullanan tablolarda işe yarar: plaka kodu değişirse ona bağlı tüm satırlar otomatik güncellenir. Yapay anahtar kullandığınızda zaten anahtar hiç değişmediği için bu eylem devreye girmez.

PostgreSQL'de aynı kısıtlar kavramsal olarak aynıdır; ek olarak DEFERRABLE INITIALLY DEFERRED seçeneğiyle kısıt kontrolünü işlemin sonuna erteleyebilirsiniz, bu da döngüsel referansları olan verileri yüklerken çok işe yarar. MySQL'de bu özellik yoktur.

Bileşik Anahtarlar ve Ara Tablolar#

Çoktan çoğa ilişkiler bir ara (bağlantı) tablosuyla kurulur ve burada bileşik birincil anahtar en doğal çözümdür. Fazladan bir id sütunu eklemek çoğu zaman gereksizdir; hem yer kaplar hem de benzersizliği ayrıca UNIQUE kısıtıyla korumanız gerekir.

CREATE TABLE urun_etiket (
  urun_id    INT UNSIGNED NOT NULL,
  etiket_id  INT UNSIGNED NOT NULL,
  eklendi    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (urun_id, etiket_id),          -- benzersizlik doğal olarak sağlanır
  KEY idx_etiket_urun (etiket_id, urun_id),  -- ters yönde arama için
  CONSTRAINT fk_ue_urun   FOREIGN KEY (urun_id)   REFERENCES urunler(id)  ON DELETE CASCADE,
  CONSTRAINT fk_ue_etiket FOREIGN KEY (etiket_id) REFERENCES etiketler(id) ON DELETE CASCADE
) ENGINE=InnoDB;

İki noktaya dikkat edin. Birincisi, bileşik anahtardaki sütun sırası önemlidir: PRIMARY KEY (urun_id, etiket_id) yalnızca "önce ürün, sonra etiket" yönünde arama yapabilir. "Bu etikete sahip ürünler" sorgusu için ters sıralı ikinci bir indeks gerekir. İkincisi, ara tabloda CASCADE genellikle doğrudur; ilişki satırının bağımsız bir anlamı yoktur. Bu tabloları sorgularken hangi birleştirme türünü kullanacağınızı SQL JOIN türleri yazısında bulabilirsiniz.

Ara tablolar 3NF tasarımın doğal ürünüdür; ilişkileri nereye böleceğinizi veritabanı normalizasyonu rehberinde adım adım anlattım.

Yabancı Anahtar Hatalarını Çözmek#

Bu üç hata neredeyse her projede karşınıza çıkar ve sebepleri birbirinden farklıdır:

Üçüncüsü en çok zaman kaybettirenidir çünkü mesaj neyin uyuşmadığını her zaman açıkça söylemez. Kontrol listesi şudur:

1. İki sütunun veri tipi birebir aynı mı? INT ile INT UNSIGNED uyumlu değildir.
2. Metin anahtarlarda karakter seti ve karşılaştırma (collation) aynı mı? utf8mb4_general_ci ile utf8mb4_unicode_ci çakışır.
3. Referans verilen sütun indeksli mi? Genelde birincil anahtar olduğu için sorun çıkmaz, ama değilse indeks gerekir.
4. Her iki tablo da InnoDB mi? MyISAM yabancı anahtarı sessizce yok sayar.
5. Ana tabloda karşılığı olmayan mevcut satırlar var mı? Kısıt eklenmeden önce veri temiz olmalı.

Ayrıntılı sebebi MySQL'in kendi tanılamasından okuyabilirsiniz:

-- Son yabancı anahtar hatasının tam açıklaması
SHOW ENGINE INNODB STATUS;
-- Çıktıda "LATEST FOREIGN KEY ERROR" bölümüne bakın

-- Öksüz satırları kısıt eklemeden ÖNCE bulun
SELECT s.siparis_no, s.musteri_id
FROM   siparisler s
LEFT   JOIN musteriler m ON m.id = s.musteri_id
WHERE  m.id IS NULL;

Toplu içe aktarma sırasında kısıt kontrollerini geçici olarak kapatabilirsiniz, ancak bunu yalnızca veriyi doğru sırayla yüklediğinizden eminseniz yapın:

SET FOREIGN_KEY_CHECKS = 0;
-- büyük içe aktarma burada
SET FOREIGN_KEY_CHECKS = 1;
-- Kapatmak, o sırada eklenen bozuk satırları GEÇERLİ saymaz; sadece kontrolü erteler.

Kontrolü tekrar açtıktan sonra yukarıdaki öksüz satır sorgusunu mutlaka çalıştırın. Genel veritabanı yönetimi işlemleri için MySQL veritabanı yönetimi yazısı da elinizin altında dursun.

Sık Yapılan Hatalar#

Yabancı anahtar sütununu indekslememek. InnoDB kısıt oluştururken gerekli indeksi kendisi ekler, ama kısıt tanımlamadıysanız o sütun indekssiz kalır ve her birleştirme tam tarama yapar.

Her tabloya körü körüne id eklemek. Ara tablolarda bileşik anahtar hem daha dar hem daha doğrudur. Fazladan id koyacaksanız en azından (a_id, b_id) üzerinde UNIQUE kısıtını unutmayın, yoksa aynı ilişki iki kez eklenebilir.

ON DELETE CASCADE'i varsayılan yapmak. Zincirleme silme, tek bir yanlış DELETE ile binlerce satırı sessizce yok edebilir. Varsayılanınız RESTRICT olsun, CASCADE'i bilinçli seçin.

Anahtar sütununda NULLa izin vermek. Yabancı anahtar sütunu zorunlu bir ilişkiyi temsil ediyorsa NOT NULL olmalıdır; aksi hâlde "müşterisi olmayan sipariş" gibi anlamsız satırlar oluşabilir.

Kısıt adlarını MySQL'e bırakmak. CONSTRAINT fk_siparis_musteri gibi açık adlar verin. Otomatik üretilen siparisler_ibfk_1 adları, hatayı okuduğunuzda hangi ilişkiden bahsedildiğini anlamanızı zorlaştırır.

Anahtar tipini tablolar arasında farklı seçmek. Ana tabloda INT UNSIGNED, bağlı tabloda BIGINT kullanmak kısıt oluşturmayı engeller. Tip, işaretlilik ve karakter seti üç tabloda da aynı olmalı.

Sıkça Sorulan Sorular#

Her tabloda birincil anahtar zorunlu mu#

Teknik olarak MySQL tabloyu birincil anahtarsız da oluşturur, ama InnoDB arka planda gizli bir kimlik üretir ve siz bunu kullanamazsınız. Ayrıca birincil anahtarsız tablolar satır tabanlı replikasyonda çok yavaş çalışır, çünkü kopya sunucu güncellenecek satırı bulmak için tam tarama yapar. Kısacası pratikte zorunludur; her tabloya bir birincil anahtar tanımlayın.

Foreign key kısıtları performansı düşürür mü#

Her INSERT ve UPDATE işleminde ana tabloda bir arama yapılır, bu da küçük bir maliyet getirir. Ancak arama birincil anahtar üzerinden noktasal olduğu için maliyet çoğu iş yükünde ölçülemeyecek kadar küçüktür. Buna karşılık kısıt olmadan oluşan öksüz satırları temizlemenin ve yanlış raporları düzeltmenin maliyeti çok daha yüksektir. Sadece çok yüksek hacimli toplu yüklemelerde kontrolleri geçici olarak kapatmak makul olabilir.

errno 150 hatasını nasıl çözerim#

Bu hata neredeyse her zaman sütun uyumsuzluğundan kaynaklanır: veri tipi, işaretlilik (UNSIGNED) veya metin sütunlarında karakter seti ile collation farklıdır. Önce iki sütunun tanımını SHOW CREATE TABLE ile yan yana karşılaştırın. Uyum tamsa referans verilen sütunun indeksli ve her iki tablonun InnoDB olduğunu doğrulayın; ayrıntılı sebep için SHOW ENGINE INNODB STATUS çıktısındaki son yabancı anahtar hatası bölümüne bakın.

ON DELETE CASCADE kullanmak güvenli mi#

Yalnızca bağlı kayıtların ana kayıt olmadan hiçbir anlamı yoksa güvenlidir; sipariş ve sipariş kalemleri buna iyi bir örnektir. Müşteri, fatura, işlem kaydı gibi bağımsız anlamı olan veriler için tehlikelidir, çünkü tek bir DELETE zincirleme olarak yıllarca birikmiş kaydı silebilir. Bu tür tablolarda RESTRICT kullanıp silme yerine yumuşak silme tercih edin.

Bileşik birincil anahtar mı yoksa ayrı bir id mi kullanmalıyım#

Ara tablolarda bileşik anahtar genellikle daha iyidir: benzersizliği doğal olarak sağlar, ekstra sütun gerektirmez ve daha dar bir kümelenmiş indeks üretir. Ayrı bir id eklemenin mantıklı olduğu durum, ilişki satırının kendisine başka tabloların referans vermesi gerektiği hâllerdir. O zaman bile ikili sütun üzerinde bir UNIQUE kısıtı eklemeyi unutmayın.

Var olan bir tabloya sonradan foreign key eklenebilir mi#

Eklenebilir, ancak önce verinin temiz olması gerekir: ana tabloda karşılığı olmayan tek bir satır bile kısıtın eklenmesini engeller. Önce LEFT JOIN ile öksüz satırları bulun, bunları düzeltin veya silin, sonra ALTER TABLE ... ADD CONSTRAINT çalıştırın. Büyük tablolarda bu işlem tabloyu bir süre kilitleyebileceği için bakım penceresinde yapmak daha güvenlidir.

Yabancı anahtar yerine uygulama kontrolü yeterli olmaz mı#

Uygulama kontrolü tek yazma yolu olsaydı yeterli olurdu, ama pratikte asla öyle değildir: toplu içe aktarmalar, bakım betikleri, elle çalıştırılan sorgular, ikinci bir servis ve zamanlanmış görevler aynı tabloya yazar. Kısıt veritabanı düzeyinde tanımlıysa bu yolların hepsi aynı kurala uymak zorunda kalır. Uygulama kontrolü kullanıcıya güzel bir hata mesajı göstermek için, veritabanı kısıtı ise verinin bozulmasını engellemek için vardır; ikisi birbirinin alternatifi değil, tamamlayıcısıdır.

Kapanış#

Anahtar tasarımı, sonradan düzeltmesi en pahalı kararlardan biridir ama kuralları sadedir: her tabloya dar ve değişmez bir birincil anahtar koyun, doğal anahtarı UNIQUE ile koruyun, yabancı anahtar kısıtlarını gerçekten tanımlayın ve ON DELETE davranışını varsayılana bırakmak yerine bilinçli seçin. Kısıt adlarını okunur vermek de ileride hata mesajlarını çözerken size zaman kazandırır.

Şemanızı kurarken ya da var olan bir veritabanına kısıt eklerken bakım penceresi ve yedek planı gerekir. Kendi MySQL veya PostgreSQL sunucunuzu tam yetkiyle yönetmek isterseniz VDS ve bulut sunucu paketlerimiz uygundur; kısıt ekleme öncesi güvenli yedek için yedekleme hizmetimize, kurulum ve izleme desteği için sunucu yönetimi hizmetimize bakabilirsiniz.