SQLite PRIMARY KEY, rowid ve AUTOINCREMENT Rehberi

Rowid nedir ve nerede saklanır

Sanal tablolar ve WITHOUT ROWID ile tanımlananlar dışındaki her tablo belgelerde "rowid tablosu" olarak geçer. Bu tablolarda veri, anahtarı rowid olan bir B-ağacı içinde tutulur. Yani SQLite'ın diskte satırı bulmak için kullandığı gerçek anahtar sizin tanımladığınız birincil anahtar değil, rowid değeridir. Rowid ile arama ve aralık sorguları, belgelere göre başka bir birincil anahtar ya da indeks üzerinden yapılan benzer bir aramadan kabaca iki kat hızlıdır; çünkü ikinci bir yapıya bakmak gerekmez.

Rowid değerine rowid, oid veya _rowid_ adlarından biriyle ulaşırsınız. Bu adlar büyük küçük harf duyarsızdır. Satır eklerken rowid'i elle de verebilirsiniz; vermezseniz SQLite tablodaki en büyük rowid'in bir fazlasını kullanır. Aşağıdaki örnekte 100 numarasını elle verdikten sonra eklenen satırın 101 aldığına dikkat edin:

CREATE TABLE not1(baslik TEXT, icerik TEXT);
INSERT INTO not1 VALUES ('ilk', 'a'), ('ikinci', 'b');
INSERT INTO not1(rowid, baslik, icerik) VALUES (100, 'elle', 'c');
INSERT INTO not1 VALUES ('sonraki', 'd');

SELECT rowid, oid, _rowid_, baslik FROM not1;
-- çıktı:
-- 1|1|1|ilk
-- 2|2|2|ikinci
-- 100|100|100|elle
-- 101|101|101|sonraki

Rowid dosyada değişken uzunluklu bir tamsayı olarak saklanır. Bu nedenle küçük ve pozitif değerler, büyük veya negatif değerlerden daha az yer kaplar. Kimlikleri 1'den başlatıp artırmanın bir faydası da budur.

INTEGER PRIMARY KEY: rowid'in takma adı

Tek sütunlu bir birincil anahtarın tanımlı tipi tam olarak INTEGER ise (harflerin büyük ya da küçük olması fark etmez) o sütun rowid'in takma adı olur. Artık aynı değere dört farklı adla ulaşabilirsiniz: üç yerleşik ad ve sizin verdiğiniz sütun adı. Sütuna NULL yazmak ya da sütunu hiç belirtmemek, SQLite'a "numarayı sen seç" demektir.

CREATE TABLE kisi(id INTEGER PRIMARY KEY, ad TEXT);
INSERT INTO kisi(ad) VALUES ('Ayşe');
INSERT INTO kisi VALUES (NULL, 'Mehmet');
INSERT INTO kisi VALUES ('10', 'Zeynep');

SELECT id, rowid, typeof(id), ad FROM kisi;
-- çıktı:
-- 1|1|integer|Ayşe
-- 2|2|integer|Mehmet
-- 10|10|integer|Zeynep

SELECT last_insert_rowid();
-- çıktı: 10

Metin olarak verilen '10' değeri kayıpsız biçimde tamsayıya çevrilebildiği için kabul edildi ve integer olarak saklandı. Takma ad sütunu sıradan sütunlardan farklıdır: SQLite'ın esnek tip sistemine rağmen burada yalnızca tamsayı durabilir. Ondalık, metin, BLOB veya NULL saklanamaz; tamsayıya kayıpsız çevrilemeyen bir değer "datatype mismatch" hatasıyla reddedilir. Esnek tip sisteminin genel kuralları için veri tipleri ve type affinity yazısına bakın.

INSERT INTO kisi VALUES ('abc', 'Can');
-- hata: datatype mismatch
INSERT INTO kisi VALUES (2.5, 'Ece');
-- hata: datatype mismatch
INSERT INTO kisi VALUES (3.0, 'Ece');     -- kayıpsız tamsayıya dönüşür, kabul edilir
UPDATE kisi SET id = NULL WHERE ad = 'Ece';
-- hata: datatype mismatch

SELECT id, ad FROM kisi;
-- çıktı:
-- 1|Ayşe
-- 2|Mehmet
-- 3|Ece
-- 10|Zeynep

Dikkat edilecek nokta şudur: INSERT sırasında NULL vermek otomatik numara ister, ama UPDATE ile var olan bir satırın kimliğini NULL yapmaya çalışmak hata verir. Kimliğin kendisini UPDATE ile başka bir tamsayıya değiştirmek ise mümkündür; rowid de diğer sütunlar gibi güncellenebilir.

INT, BIGINT ve diğer yazımlar takma ad oluşturmaz

Kural kelimesi kelimesine uygulanır: tip adının tam olarak INTEGER olması gerekir. INT, BIGINT, UNSIGNED INTEGER gibi yazımlar sütuna tamsayı eğilimi verir ama onu rowid'in takma adı yapmaz. Bu durumda tabloda gizli bir rowid ayrıca bulunur, birincil anahtar için de ayrı bir benzersiz indeks oluşturulur. Daha da şaşırtıcısı, rowid tablolarında birincil anahtar sütunu NULL kabul eder:

CREATE TABLE urun(kod INT PRIMARY KEY, ad TEXT);
INSERT INTO urun VALUES (NULL, 'kalem'), (NULL, 'silgi');
INSERT INTO urun VALUES (7, 'defter');
INSERT INTO urun VALUES (7, 'cetvel');
-- hata: UNIQUE constraint failed: urun.kod

SELECT rowid, kod, ad FROM urun;
-- çıktı:
-- 1||kalem
-- 2||silgi
-- 3|7|defter

SELECT name, origin FROM pragma_index_list('urun');
-- çıktı: sqlite_autoindex_urun_1|pk

İki satırın kod değeri NULL olduğu hâlde benzersizlik ihlali oluşmadı; çünkü benzersizlik kontrolünde NULL değerler birbirinden farklı sayılır. Belgeler bunu SQLite'ın erken sürümlerindeki bir hata olarak açıklar: SQL standardına göre birincil anahtar NULL olamaz, ancak düzeltme eski uygulamaları bozacağı için davranış korunmuş ve belgelenmiştir. Birincil anahtar sütunu INTEGER PRIMARY KEY değilse, tablo WITHOUT ROWID veya STRICT değilse ya da sütuna açıkça NOT NULL yazılmamışsa NULL kabul edilir. NULL davranışının diğer yönleri için NULL davranışı ve coalesce yazısına bakabilirsiniz.

Pratik sonuç basittir: metin veya bileşik birincil anahtar kullanıyorsanız her sütuna NOT NULL ekleyin. Böyle tablolarda rowid gereksiz bir ek katman hâline geldiği için WITHOUT ROWID tablolar de değerlendirmeye değer.

PRIMARY KEY DESC tuzağı

Belgelerde açıkça belirtilen bir istisna daha var: sütun tanımının içinde INTEGER PRIMARY KEY DESC yazarsanız sütun takma ad olmaz. Aynı sıralama tablo kısıtı olarak, yani PRIMARY KEY(x DESC) biçiminde yazıldığında ise takma ad olur. Bu tutarsızlık bilinçli bir tasarım değil, eski bir hatanın uyumluluk adına korunmasıdır. Bir sütunun takma ad olup olmadığını anlamanın kolay yolu, tablo için ayrı bir indeks oluşturulup oluşturulmadığına bakmaktır:

CREATE TABLE t1(x INTEGER PRIMARY KEY ASC, y);
CREATE TABLE t2(x INTEGER, y, PRIMARY KEY(x DESC));
CREATE TABLE t3(x INTEGER PRIMARY KEY DESC, y);
CREATE TABLE t4(x BIGINT PRIMARY KEY, y);

-- Ayrı bir indeks varsa sütun rowid takma adı değildir
SELECT 't1', count(*) FROM pragma_index_list('t1');
-- çıktı: t1|0
SELECT 't2', count(*) FROM pragma_index_list('t2');
-- çıktı: t2|0
SELECT 't3', count(*) FROM pragma_index_list('t3');
-- çıktı: t3|1
SELECT 't4', count(*) FROM pragma_index_list('t4');
-- çıktı: t4|1

Otomatik numara nasıl seçilir

Kimlik verilmeden yapılan bir eklemede SQLite normalde tablodaki en büyük rowid'in bir fazlasını kullanır; tablo boşsa 1 ile başlar. En büyük rowid 9223372036854775807 değerine, yani 64 bitlik işaretli tamsayının üst sınırına ulaşmışsa kullanılmayan pozitif bir değer bulana kadar rastgele adaylar dener. Makul sayıda denemede boş değer bulamazsa ekleme SQLITE_FULL hatasıyla başarısız olur. Elle negatif değer eklemediğiniz sürece otomatik üretilen değerler her zaman sıfırdan büyüktür.

Bu algoritmanın önemli bir sonucu var: en büyük numaralı satırı silerseniz o numara bir sonraki eklemede yeniden kullanılabilir. Ortadaki satırları silmek numaraları geri getirmez, çünkü en büyük değer değişmemiştir. Ancak en sondaki satır silindiğinde yeni satır onun numarasını alır. Çoğu uygulama için bu sorun değildir; fakat kimliği dış sistemlere veren, örneğin bir fatura numarasını müşteriye e-posta ile gönderen bir uygulamada silinmiş bir kaydın numarasının yeni bir kayda geçmesi karışıklık yaratabilir.

AUTOINCREMENT ne değiştirir

INTEGER PRIMARY KEY AUTOINCREMENT yazdığınızda seçim kuralı değişir: yeni rowid, o tabloda şimdiye kadar var olmuş en büyük rowid'den en az bir fazla olur. Silinmiş satırların numaraları geri gelmez ve otomatik üretilen değerler her zaman artan sırada olur. Aşağıda aynı işlemleri iki tabloda yapıyoruz:

CREATE TABLE normal(id INTEGER PRIMARY KEY, ad TEXT);
CREATE TABLE artan(id INTEGER PRIMARY KEY AUTOINCREMENT, ad TEXT);
INSERT INTO normal(ad) VALUES ('a'), ('b'), ('c');
INSERT INTO artan(ad) VALUES ('a'), ('b'), ('c');

-- En büyük kimliğe sahip satırı iki tablodan da silelim
DELETE FROM normal WHERE id = 3;
DELETE FROM artan WHERE id = 3;
INSERT INTO normal(ad) VALUES ('d');
INSERT INTO artan(ad) VALUES ('d');

SELECT 'normal', id, ad FROM normal;
-- çıktı:
-- normal|1|a
-- normal|2|b
-- normal|3|d
SELECT 'artan', id, ad FROM artan;
-- çıktı:
-- artan|1|a
-- artan|2|b
-- artan|4|d

SELECT name, seq FROM sqlite_sequence;
-- çıktı: artan|4

Fark açık: normal tabloda silinen 3 numarası yeni satıra verildi, AUTOINCREMENT kullanan tabloda ise sıradaki numara 4 oldu. SQLite en büyük değeri hatırlamak için sqlite_sequence adlı iç tabloyu kullanır. Bu tablo, şemada AUTOINCREMENT içeren ilk tablo oluşturulduğunda kendiliğinden yaratılır; ilgili tabloya ilk yazmada bir satır eklenir ve en büyük değeri artıran her eklemede güncellenir.

Tablo tamamen boşaltılsa bile sayaç sıfırlanmaz. Öte yandan geri alınan (ROLLBACK edilen) işlemlerdeki numaralar hesaba katılmaz; belgelere göre yalnızca onaylanmış işlemlerdeki rowid değerleri dikkate alınır ve geri alınan değerler yeniden kullanılabilir:

DELETE FROM artan;                       -- tablo tamamen boşaldı
INSERT INTO artan(ad) VALUES ('e');
SELECT id, ad FROM artan;
-- çıktı: 5|e

BEGIN;
INSERT INTO artan(ad) VALUES ('geri');   -- bu satır 6 numarayı alır
ROLLBACK;
INSERT INTO artan(ad) VALUES ('f');
SELECT id, ad FROM artan;
-- çıktı:
-- 5|e
-- 6|f

"Artan" ile "ardışık" aynı şey değildir. Örneğin bir ekleme benzersizlik ihlali yüzünden başarısız olursa, belgelere göre o denemede kullanılan numara sonraki eklemelerde kullanılmayabilir ve dizide boşluk oluşabilir. AUTOINCREMENT numaraların artacağını garanti eder, aralarında boşluk olmayacağını değil. Fatura gibi kesintisiz numara gerektiren durumlarda numarayı uygulamanızda ayrı bir sayaçla üretmeniz gerekir.

Kısıtlar ve sqlite_sequence ile oynamak

AUTOINCREMENT yalnızca INTEGER PRIMARY KEY sütununda geçerlidir ve WITHOUT ROWID tablolarda kullanılamaz. sqlite_sequence tablosunun içeriğini sıradan UPDATE, INSERT ve DELETE komutlarıyla değiştirebilirsiniz; belgeler bunun algoritmayı bozabileceği konusunda uyarır. Sayacın UPDATE ile değiştirilen rowid'leri izlemediğini, yalnızca eklemeleri izlediğini de unutmayın.

CREATE TABLE hatali(id INT PRIMARY KEY AUTOINCREMENT);
-- hata: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

-- sqlite_sequence elle değiştirilebilir, ama sonucunu bilerek yapın
UPDATE sqlite_sequence SET seq = 1000 WHERE name = 'artan';
INSERT INTO artan(ad) VALUES ('g');
SELECT id, ad FROM artan;
-- çıktı:
-- 5|e
-- 6|f
-- 1001|g

Üst sınıra ulaşıldığında iki yöntem farklı davranır. AUTOINCREMENT kullanan tablo, en büyük olası değer bir kez kullanıldıktan sonra yeni eklemeyi kabul etmez. Normal tablo ise rastgele boş bir değer arar:

CREATE TABLE ust(id INTEGER PRIMARY KEY AUTOINCREMENT, ad TEXT);
INSERT INTO ust VALUES (9223372036854775807, 'son');
INSERT INTO ust(ad) VALUES ('bir daha');
-- hata: database or disk is full

CREATE TABLE ust2(id INTEGER PRIMARY KEY, ad TEXT);
INSERT INTO ust2 VALUES (9223372036854775807, 'son');
INSERT INTO ust2(ad) VALUES ('bir daha');   -- rastgele, kullanılmayan pozitif bir değer seçilir
SELECT count(*), min(id) > 0 FROM ust2;
-- çıktı: 2|1

AUTOINCREMENT kullanmalı mısınız

Belgelerin önerisi nettir: AUTOINCREMENT ek işlemci, bellek, disk alanı ve disk okuma yazma maliyeti getirir ve gerçekten gerekmedikçe kullanılmamalıdır; çoğu zaman da gerekmez. Her eklemede sqlite_sequence tablosunun okunması ve gerekirse güncellenmesi, eklemeleri biraz yavaşlatır.

Kimlikleri yalnızca tablolar arasındaki ilişkiler için kullanıyorsanız düz INTEGER PRIMARY KEY yeterlidir. Silinmiş bir kaydın numarasının bir daha asla görünmemesi gerçekten önemliyse, örneğin numaralar kayıt dışına çıkıyor ve denetim izinde kullanılıyorsa, AUTOINCREMENT tercih edilebilir.

Rowid'e doğrudan güvenmeyin

Tablonuzda INTEGER PRIMARY KEY yoksa rowid kalıcı değildir. Belgeler özellikle VACUUM komutunun bu tür tablolarda rowid değerlerini değiştirebileceğini belirtir. Başka bir tablodan ya da uygulama kodundan bir satıra kalıcı olarak işaret etmek istiyorsanız takma ad sütunu tanımlayın. Ayrıca yabancı anahtarlar rowid'e başvuramaz; üst tablodaki anahtar mutlaka adı olan bir sütun olmalıdır. Bunun ayrıntıları foreign key yazısında anlatılıyor.

Bir başka tuzak ad çakışmasıdır. Tabloda rowid, oid ya da _rowid_ adında tanımlı bir sütun varsa o ad artık gizli rowid'i değil sizin sütununuzu gösterir. Diğer iki ad gizli değere ulaşmaya devam eder:

CREATE TABLE garip(rowid TEXT, deger INT);
INSERT INTO garip VALUES ('benim', 5);

SELECT rowid, oid, deger FROM garip;
-- çıktı: benim|1|5

Bu tür adlarla sütun tanımlamaktan kaçının. SQLite 3.54.0 sürüm notlarına göre ALTER TABLE artık bu özel adlarla sütun ekleme, silme veya yeniden adlandırma girişimlerinde hata veriyor; şema değişikliklerinin geri kalanı için ALTER TABLE yazısına bakın.

Son eklenen kimliği almak

Otomatik üretilen numarayı öğrenmenin SQL tarafındaki yolu last_insert_rowid() fonksiyonudur; aynı bağlantıda yapılan son başarılı eklemenin rowid değerini döndürür. Daha yeni ve çoğu zaman daha kullanışlı yol INSERT ... RETURNING id yazmaktır; bu sayede tek komutta hem ekler hem de değeri geri alırsınız. Ayrıntılar INSERT, UPDATE, DELETE yazısında. Programlama dilleri bu değeri kendi API'leriyle de sunar; örneğin Python sqlite3 veya Node.js sqlite modülü yazılarına bakabilirsiniz.

Sık yapılan hatalar

• Her tabloya AUTOINCREMENT eklemek: Otomatik numara için gerekmez; yalnızca numaraların yeniden kullanılmaması gerekiyorsa ekleyin.
• INT PRIMARY KEY yazıp takma ad beklemek: Takma ad için tip adı tam olarak INTEGER olmalıdır. INT yazarsanız ayrı bir indeks ve gizli bir rowid oluşur.
• Metin birincil anahtarda NOT NULL unutmak: Rowid tablolarında bu sütun NULL kabul eder ve birden fazla NULL satır oluşabilir.
• Kimliklerin ardışık olacağını varsaymak: Silmeler, başarısız eklemeler ve geri alınan işlemler boşluk bırakabilir.
• Takma ad olmayan rowid değerini dışarıya vermek: VACUUM sonrasında değişebilir; kalıcı kimlik için INTEGER PRIMARY KEY tanımlayın.

Sık Sorulan Sorular

AUTOINCREMENT olmadan id sütunu otomatik artar mı?

Evet. INTEGER PRIMARY KEY sütununa değer vermediğinizde veya NULL verdiğinizde SQLite genellikle en büyük mevcut değerin bir fazlasını kullanır. AUTOINCREMENT yalnızca silinmiş numaraların yeniden kullanılmasını engeller.

sqlite_sequence tablosunu silebilir miyim?

Bu tablo AUTOINCREMENT kullanan tabloların sayaçlarını tutar. İçeriğini sıradan komutlarla değiştirmek mümkündür, ancak belgeler bunun numara üretimini bozabileceğini belirtir. Bir sayacı ileri almak gibi özel bir ihtiyacınız yoksa dokunmayın.

Kimlik sütununu sonradan AUTOINCREMENT yapabilir miyim?

ALTER TABLE bu değişikliği doğrudan desteklemez. Tabloyu yeni tanımla oluşturup veriyi kopyalamanız gerekir; güvenli adımlar ALTER TABLE yazısındaki genel yöntemde anlatılıyor.

Bir sütunun rowid takma adı olup olmadığını nasıl anlarım?

Tanımın tek sütunlu ve tipi tam olarak INTEGER olan bir birincil anahtar olup olmadığına bakın. Emin olmak için pragma_index_list ile tabloya ait birincil anahtar indeksi olup olmadığını kontrol edebilirsiniz; takma ad sütunu için ayrı bir indeks oluşturulmaz. Şema sorgulama yolları için tabloları listeleme yazısına bakı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 · WITHOUT ROWID tablolar · CREATE TABLE ve kısıtlamalar · STRICT tablolar · Foreign key · UPSERT ve ON CONFLICT