MariaDB Foreign Key ve CHECK Kısıtlamaları: ON DELETE, ON UPDATE ve Sınırlar

MariaDB'deki kısıtlama türleri

Kısıtlamalar, tabloya eklenebilecek veriye sınır koyar ve veri bütünlüğünü uygulama kodu yerine veritabanında güvenceye alır. Belge dört tablo kısıtlaması sayar:

Genel sözdizimi [CONSTRAINT [symbol]] constraint_expression biçimindedir. Hangi tablolarda hangi kısıtlamaların bulunduğunu information_schema.TABLE_CONSTRAINTS tablosundan sorgulayabilirsiniz; bu tablo her kısıtlamanın şemasını, adını, tablosunu ve türünü (PRIMARY KEY, UNIQUE, FOREIGN KEY gibi) listeler. Birincil anahtar ve benzersiz index ayrıntıları index yazısında ele alındığı için burada yabancı anahtar ve CHECK üzerinde duruyoruz.

Yabancı anahtar nedir?

Yabancı anahtar, "alt tablo" (child) adı verilen bir tablodaki bir veya daha fazla sütunun, "üst tablo" (parent) adı verilen tablodaki bir veya daha fazla sütuna başvurmasıdır. Kısıtlama tanımlıysa alt tablodaki her satırın üst tabloda eşleşen bir satırı olmalıdır; birden fazla alt satır aynı üst satıra bağlanabilir. Bir alt satır, yabancı anahtar değerlerinin tamamı üst satırdaki değerlerle aynıysa eşleşmiş sayılır. Önemli bir ayrıntı: yabancı anahtar değerlerinden en az biri NULL ise satırın üst satırı yoktur ama yine de kabul edilir. Bu yüzden ilişkinin zorunlu olmasını istiyorsanız alt tablodaki sütunu NOT NULL tanımlamalısınız.

Tanım sözdizimi şöyledir:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

symbol isteğe bağlıdır; verilmezse MariaDB otomatik bir ad atar. Bu ad hata mesajlarında göründüğü için anlamlı bir ad vermek hata ayıklamayı kolaylaştırır. Sürüm notu: belgeye göre MariaDB 12.1'den itibaren yabancı anahtar adlarının tablo başına benzersiz olması yeterlidir; 12.1 öncesinde veritabanı başına benzersiz olmaları gerekiyordu. Aynı veritabanındaki iki tabloda aynı kısıtlama adını kullanan bir şemayı eski bir sürüme taşıyacaksanız bunu hesaba katın. Alt tabloda yabancı anahtar için uygun bir index yoksa ve siz açıkça oluşturmadıysanız MariaDB indexi kendisi oluşturur ve ona index_name adını verir.

ON DELETE ve ON UPDATE eylemleri

Üst tablodaki bir satır silindiğinde veya başvurulan sütunun değeri değiştiğinde, en az bir alt satır varsa MariaDB yan tümcede belirttiğiniz eylemi uygular. Yan tümceyi yazmazsanız varsayılan RESTRICT'tir.

SET NULL kullanacaksanız alt tablodaki sütun NOT NULL tanımlanmamış olmalıdır, aksi hâlde işlem başarılı olamaz. Yabancı anahtar nedeniyle tetiklenen DELETE ve UPDATE işlemleri tetikleyicileri çalıştırmaz ve Com_delete, Com_update durum değişkenlerine de sayılmaz. Bir CASCADE silmesinin ardından denetim kaydı tutan bir tetikleyiciye güveniyorsanız bu davranışı bilmeniz gerekir; tetikleyiciler için MariaDB trigger yazısına bakın.

Uygulamalı örnek: yazarlar ve kitaplar

Belgedeki örnekte author ve book adlı iki tablo vardır. book.author_id, author.id sütununa başvurur; kısıtlamanın adı hata mesajlarında görünsün diye açıkça fk_book_author verilmiştir. Silme işleminde CASCADE, güncellemede RESTRICT seçilmiştir:

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

İki sütunun tipine dikkat edin: ikisi de SMALLINT UNSIGNED. Tamsayı tiplerinde boyut ve işaretin aynı olması bir gereksinimdir. Var olmayan bir yazara kitap eklemeye çalışmak hata verir:

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Önce yazarı ekleyip ardından kitabı LAST_INSERT_ID() ile bağlamak doğru sıradır:

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

İkinci yazarı sildiğinizde ON DELETE CASCADE silmeyi alt tabloya yayar ve o yazarın iki kitabı da kaybolur:

DELETE FROM author WHERE name = 'H.P. Lovecraft';

SELECT * FROM book;
+----+--------------+-----------+
| id | title        | author_id |
+----+--------------+-----------+
|  3 | Necronomicon |         1 |
+----+--------------+-----------+

ON UPDATE RESTRICT ise alt satırı olan bir yazarın kimliğini değiştirmeyi engeller:

UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Bu örnek iki hata kodunu ayırt etmeyi öğretir: 1452 alt tabloya uyumsuz satır eklendiğinde, 1451 ise üst tablodaki başvurulan satır değiştirilmek ya da silinmek istendiğinde gelir. Hata kodlarının toplu açıklaması için MariaDB sık hatalar ve çözümleri yazısına bakabilirsiniz. Veri ekleme ve silme ifadelerinin ayrıntıları ise MariaDB INSERT, UPDATE, DELETE yazısında.

Gereksinimler ve sınırlamalar

Yabancı anahtar oluşturmanın en sık başarısız olma nedeni aşağıdaki gereksinimlerden birinin karşılanmamasıdır:

• Üst tablodaki başvurulan sütunlar bir index ya da bir indexin öneki olmalıdır.
• Yabancı anahtar sütunu ile başvurulan sütun aynı ya da benzer tipte olmalıdır; tamsayılarda boyut ve işaret de aynı olmalıdır.
• Metin tiplerinde iki sütunun karakter seti ve collation değeri aynı olmalıdır. Belge açıkça utf8mb4_general_ci ile utf8mb4_uca1400_ai_ci gibi yalnızca collation'ı farklı iki sütun arasında yabancı anahtarın reddedildiğini söyler.
• Üst ve alt tablo aynı depolama motorunu kullanmalı; TEMPORARY veya bölümlenmiş (partitioned) tablo olmamalıdır. İki tablo aynı tablo da olabilir.
• Her iki taraf PERSISTENT sütun olabilir, ancak bu durumda ON UPDATE CASCADE, ON UPDATE SET NULL ve ON DELETE SET NULL kullanılamaz.

Sınırlamalar listesi de kısa ama kritiktir: yabancı anahtarı yalnızca InnoDB destekler, görünümlerle (view) kullanılamaz, SET DEFAULT desteklenmez ve ON UPDATE CASCADE zincir içinde daha önce güncellediği aynı tabloyu yeniden güncellemeye çalışırsa RESTRICT gibi davranır. Alt tablodaki sütunlar bir BTREE indexin tamamı ya da en sol kısmı olmalıdır; HASH, RTREE veya FULLTEXT uygun değildir. Index önekleri desteklenmediği için TEXT ve BLOB sütunları yabancı anahtar olamaz. İndexlenmiş generated sütunlar da InnoDB yabancı anahtar indexi olarak desteklenmez. Bölümlenmiş tablolar ne yabancı anahtar içerebilir ne de bir yabancı anahtar tarafından başvurulabilir; bölümleme planlıyorsanız MariaDB tablo bölümleme yazısındaki sınırlara bakın.

Yanlış sütuna başvurmanın sonucunu belge şöyle gösterir: a tablosunda indexsiz not_key sütununa başvuran tablo oluşturulamaz, birincil anahtara başvuran tablo ise sorunsuz oluşturulur ve MariaDB alt tarafta indexi kendisi ekler:

CREATE TABLE a(a_key INT PRIMARY KEY, not_key INT);

CREATE TABLE b(for_key INT REFERENCES a(not_key));
ERROR 1005 (HY000): Can't create table `test`.`b` 
  (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE c(for_key INT REFERENCES a(a_key));

Buradaki sütun düzeyindeki REFERENCES kısa yazımına dikkat edin: belge, MariaDB'nin bu biçimdeki kısıtlamayı mümkün olduğunda uyguladığını söyler ve c tablosu için SHOW CREATE TABLE çıktısında otomatik oluşturulmuş c_ibfk_1 adlı kısıtlamayı ve for_key indexini gösterir.

Bütünlüğü koruyan diğer denetimler

MariaDB yabancı anahtar varken bazı işlemleri de denetler. Başka bir tablonun yabancı anahtarıyla başvurulan bir tabloyu silmeye çalışmak 1217 hatası üretir. Yabancı anahtar içeren bir tabloda TRUNCATE TABLE, WHERE içermeyen bir DELETE gibi çalıştırılır; yani kısıtlamalar her satır için uygulanır ve hızlı boşaltma avantajı kaybolur.

foreign_key_checks ile denetimleri geçici kapatmak

foreign_key_checks sistem değişkenini 0 yapmak yabancı anahtar denetimlerini askıya alır; belgeye göre bu, büyük miktarda veri yüklerken eklemeyi hızlandırır. Ancak bu ayar yalnızca veri denetimini askıya alır, kısıtlamayı geçersiz bırakacak şema değişikliklerine izin vermez. Ayar ne olursa olsun yabancı anahtarda kullanılan bir sütun silinemez, yeniden adlandırılamaz ve tipi, karakter seti veya collation'ı gibi değerlerin karşılaştırılma biçimini değiştiren bir şekilde değiştirilemez. Toplu veri yükleme senaryoları için MariaDB LOAD DATA ve mariadb-import yazısına bakın.

Yabancı anahtar sütununun karakter setini değiştirmek

İki metin sütunu aynı karakter seti ve collation'ı paylaşmak zorunda olduğundan tablolardan yalnızca birini dönüştürmek kısıtlamayı bozar ve MariaDB buna izin vermez: alt tabloyu dönüştürmek 1832, üst tabloyu dönüştürmek 1833 hatası verir. Belge, foreign_key_checks değerini 0 yapmanın ya da ALGORITHM=COPY, ALGORITHM=INPLACE istemenin bu yasağı kaldırmadığını özellikle vurgular. Doğru yol kısıtlamayı düşürmek, iki tabloyu dönüştürmek ve kısıtlamayı yeniden kurmaktır:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE child
  DROP FOREIGN KEY fk_name,
  MODIFY col VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE parent
  MODIFY col VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE child
  ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent (col);

SET FOREIGN_KEY_CHECKS=1;

Sıra önemlidir: alt tablodaki kısıtlama hâlâ dururken üst tablo dönüştürülemez. Ayrıca kısıtlamayı foreign_key_checks 0 iken yeniden oluşturursanız mevcut satırlar doğrulanmaz; doğrulanmalarını istiyorsanız ADD CONSTRAINT ifadesinden önce değişkeni 1'e döndürün. Dönüştürmeniz gereken kısıtlamaları listelemek için belgedeki sorgu şudur:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE TABLE_SCHEMA = DATABASE()
   AND REFERENCED_TABLE_NAME IS NOT NULL;

Karakter seti seçimi ve utf8mb4 geçişinin genel çerçevesi için MariaDB karakter seti ve collation yazısına bakın. Yabancı anahtar bilgilerini ayrıca information_schema.REFERENTIAL_CONSTRAINTS tablosundan (her kısıtlamanın UPDATE_RULE ve DELETE_RULE değerleriyle) ve SHOW CREATE TABLE çıktısından da okuyabilirsiniz.

CHECK kısıtlamaları

CHECK kısıtlamaları uygulanır ve iki biçimde tanımlanabilir: sütun tanımının parçası olarak CHECK(ifade) ya da tablo düzeyinde CONSTRAINT [ad] CHECK (ifade). Bir satır eklenmeden veya güncellenmeden önce tüm kısıtlamalar tanımlandıkları sırayla değerlendirilir; herhangi biri yanlış dönerse satır eklenmez ya da güncellenmez. Kısıtlama içinde çoğu deterministik fonksiyonu, kullanıcı tanımlı fonksiyonlar dahil, kullanabilirsiniz. AUTO_INCREMENT sütunlarına CHECK içinde izin verilmez.

CREATE TABLE t1 (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater CHECK (a>b));

INSERT INTO t1(a) VALUES (1);
ERROR 4022 (23000): CONSTRAINT `a` failed for `test`.`t1`

INSERT INTO t1(a,b) VALUES (3,4);
ERROR 4022 (23000): CONSTRAINT `a_greater` failed for `test`.`t1`

INSERT INTO t1(a,b) VALUES (4,3);
Query OK, 1 row affected (0.04 sec)

Hata mesajında görünen ad, sütun düzeyindeki kısıtlamalarda sütunun adıdır (a), tablo düzeyinde adlandırılmış kısıtlamada ise verdiğiniz addır (a_greater). Tablo düzeyinde ad vermezseniz kısıtlama otomatik bir ad alır; bu sayede onu sonradan ALTER TABLE ... DROP CONSTRAINT ile silebilirsiniz. Ekleme ve silme şöyledir:

ALTER TABLE t1 ADD CONSTRAINT a_greater CHECK (a>b);
ALTER TABLE t1 DROP CONSTRAINT a_greater;

Parantez yerine dikkat

Belgedeki öğretici örnek, ad uzunluğunu denetleyen bir kısıtlamada parantezin yanlış yere konmasını gösterir. CHECK (CHAR_LENGTH(name)>2) doğru denetimdir; CHECK (CHAR_LENGTH(name>2)) ise önce name>2 karşılaştırmasını sayısal olarak yapar ve "Io" gibi kısa bir adı reddetmek yerine satırı bir uyarıyla kabul eder:

CREATE TABLE t3 (name VARCHAR(30) CHECK (CHAR_LENGTH(name>2)), start_date DATE, 
  end_date DATE CHECK (start_date IS NULL OR end_date IS NULL OR start_date<end_date));
Query OK, 0 rows affected (0.32 sec)

INSERT INTO t3(name, start_date, end_date) VALUES('Io', '2003-12-15', '2014-11-09');
Query OK, 1 row affected, 1 warning (0.04 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Io' |
+---------+------+----------------------------------------+

Aynı örnekte tarih denetimi, NULL değerleri bilinçli olarak serbest bırakır: start_date IS NULL OR end_date IS NULL OR start_date < end_date. Bu kalıp, yalnızca iki tarih de doluyken sıralamayı zorunlu kılmak için kullanışlıdır.

Tüm kısıtlama ifadesi denetimlerini check_constraint_checks değişkenini OFF yaparak kapatabilirsiniz. Belge bunu, bazı kısıtlamaları ihlal eden bir tabloyu yükleyip hatalı satırları sonradan SQL ile bulup düzeltmek istediğiniz durumlar için önerir. Replikasyon ortamında satır tabanlı replikasyonda kısıtlamaları yalnızca birincil sunucu denetler, deyim tabanlı replikasyonda kopyalar da denetler; bu yüzden kısıtlamaların iki tarafta aynı ve deterministik olması gerekir. Ayrıntılar için MariaDB replikasyon kurulumu yazısına bakın.

Sık yapılan hatalar

• Tip uyuşmazlığı: INT bir sütunu INT UNSIGNED bir birincil anahtara bağlamak ya da farklı collation kullanan metin sütunlarını eşlemek kısıtlamanın oluşmasını engeller.
• Indexsiz sütuna başvurmak: üst tablodaki sütun bir index veya index öneki değilse 1005 hatası ve "Foreign key constraint is incorrectly formed" mesajı alırsınız.
• MyISAM veya bölümlenmiş tablo kullanmak: yabancı anahtar yalnızca InnoDB'de çalışır ve bölümlenmiş tablolarda kullanılamaz.
• NULL'a izin verip ilişkiyi zorunlu sanmak: yabancı anahtar sütununda NULL olan satır kabul edilir.
• CASCADE'in tetikleyici çalıştıracağını beklemek: yabancı anahtar eylemleri tetikleyicileri etkinleştirmez.
• foreign_key_checks=0 ile şema değiştirmeye çalışmak: bu ayar sütun tipi veya collation değişikliği yasağını kaldırmaz.
• CHECK içinde parantezi yanlış koymak: ifade hata vermeden yanlış şeyi denetleyebilir; kısıtlamayı geçersiz ve geçerli birer satırla test edin.

Sık Sorulan Sorular

Var olan bir tabloya yabancı anahtar nasıl eklenir?

ALTER TABLE alt_tablo ADD CONSTRAINT ad FOREIGN KEY (sutun) REFERENCES ust_tablo (sutun); biçimini kullanın. Mevcut satırların kısıtlamaya uyması gerekir; foreign_key_checks 0 iken eklenen kısıtlama mevcut satırları doğrulamaz. Şema değiştirme seçenekleri için MariaDB ALTER TABLE yazısına bakın.

Yabancı anahtarı nasıl silerim?

ALTER TABLE alt_tablo DROP FOREIGN KEY ad; ifadesiyle. Adı bilmiyorsanız SHOW CREATE TABLE çıktısına ya da KEY_COLUMN_USAGE sorgusuna bakın.

ON DELETE yazmazsam ne olur?

Belgeye göre ON DELETE ve ON UPDATE için varsayılan davranış RESTRICT'tir; alt satırı olan üst satırın silinmesi veya anahtarının değiştirilmesi 1451 hatasıyla engellenir.

CHECK kısıtlamaları uygulanıyor mu, yoksa yalnızca belgeleme mi?

Uygulanır. Kısıtlama yanlış dönerse satır eklenmez veya güncellenmez ve 4022 hatası gelir. Yalnızca check_constraint_checks OFF yapıldığında denetim devre dışı kalır.

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: MariaDB öğrenme rehberi · Index oluşturma · Veritabanı ve tablo oluşturma · ALTER TABLE · Transaction ve izolasyon · Karakter seti ve collation