KAL
Menu

Mengubah Schema

Kemampuan dan batas ALTER TABLE serta pola perubahan schema yang aman.

Daftar isi

Kebutuhan data berubah seiring aplikasi berkembang, sehingga definisi tabel jarang bertahan selamanya. SQLite menyediakan ALTER TABLE untuk sebagian kecil perubahan schema, dan menyisanya pada prosedur manual yang lebih hati-hati. Topik ini membahas perubahan yang didukung ALTER TABLE, batas-batasnya, serta pola tabel baru dan penyalinan untuk perubahan yang rumit.

Perubahan yang didukung ALTER TABLE#

ALTER TABLE menangani empat kelompok perubahan. ALTER TABLE ... RENAME TO mengganti nama tabel dan telah ada sejak lama, sedangkan ALTER TABLE ... RENAME COLUMN mengganti nama kolom sejak versi 3.25.0 (2018). ALTER TABLE ... ADD COLUMN menambahkan kolom baru di posisi terakhir, dan ALTER TABLE ... DROP COLUMN menghapus kolom sejak versi 3.35.0 (2021). Versi sqlite3 yang dipakai pada jalur ini, yaitu 3.54.1, mendukung semuanya.

Di luar keempat kelompok tersebut, ALTER TABLE tidak menyediakan apa pun. Mengubah tipe sebuah kolom, mengurutkan ulang kolom, atau mengganti constraint pada kolom lama tidak punya sintaks sama sekali, karena SQLite menolak menerapkan perubahan semacam itu langsung pada tabel yang berisi data. Perubahan-perubahan jenis ini dikerjakan melalui pola tabel baru dan penyalinan yang dibahas di bagian akhir.

Peragakan RENAME dan ADD COLUMN#

Penggantian nama adalah perubahan yang paling ringan. Blok berikut mengganti tabel anggota menjadi pengguna, lalu mengganti kolom nama menjadi nama_lengkap, dan mengisi satu baris untuk membuktikan struktur barunya.

sql Contoh: RENAME TABLE dan RENAME COLUMN 
CREATE TABLE anggota (
    id   INTEGER PRIMARY KEY,
    nama TEXT NOT NULL
);

ALTER TABLE anggota RENAME TO pengguna;

ALTER TABLE pengguna RENAME COLUMN nama TO nama_lengkap;

INSERT INTO pengguna (nama_lengkap) VALUES ('Citra');

SELECT id, nama_lengkap FROM pengguna;
text Keluaran 
1|Citra

Query pada baris terakhir berhasil memakai nama tabel dan kolom yang baru, sehingga kedua penggantian terbukti berlaku. Sejak versi 3.25.0, penggantian nama juga diteruskan secara otomatis ke trigger, view, dan klausa REFERENCES pada tabel lain yang merujuk objek yang berganti nama. Perilaku ini membuat RENAME jauh lebih aman daripada menghapus lalu membuat ulang tabel dengan nama baru, yang memutus seluruh referensi kepadanya.

ADD COLUMN bekerja pada arah yang berbeda, yaitu memperluas tabel tanpa mengubah kolom lama. Blok bash berikut membangun tabel buku dengan dua kolom, menambahkan kolom tahun, lalu menampilkan isinya.

bash 
# bangun basis data awal
rm -f perpustakaan.db
sqlite3 perpustakaan.db <<'SQL'
CREATE TABLE buku (
    id        INTEGER PRIMARY KEY,
    judul     TEXT NOT NULL,
    pengarang TEXT
);
INSERT INTO buku (judul, pengarang) VALUES
    ('Dasar Algoritma', 'Dewi'),
    ('Peta Langit', 'Ali');
SQL

# tambahkan kolom tahun pada tabel buku
sqlite3 perpustakaan.db "ALTER TABLE buku ADD COLUMN tahun INTEGER;"

# kolom baru bernilai NULL untuk seluruh baris lama
sqlite3 perpustakaan.db <<'SQL'
.nullvalue NULL
SELECT id, judul, pengarang, tahun FROM buku;
SQL
text Keluaran 
1|Dasar Algoritma|Dewi|NULL
2|Peta Langit|Ali|NULL

Keluaran menunjukkan bahwa kolom tahun berisi NULL untuk seluruh baris lama, karena SQLite tidak memiliki data lama yang bisa diisikan. Kolom baru juga selalu ditempatkan pada posisi terakhir, dan ALTER TABLE tidak dapat menambahkan kolom PRIMARY KEY. Nilai untuk kolom baru kemudian diisi per baris dengan UPDATE, atau langsung ditetapkan sebagai DEFAULT pada definisinya.

Batas ALTER TABLE#

Tiga batas utama ALTER TABLE paling mudah dipahami dari pesan errornya. Blok berikut menjalankan tiga perubahan yang semuanya ditolak, yaitu menambahkan kolom NOT NULL tanpa DEFAULT pada tabel yang sudah berisi baris, menghapus kolom PRIMARY KEY, dan mencoba mengubah tipe kolom dengan sintaks yang tidak ada.

sql Contoh: tiga batas ALTER TABLE 
CREATE TABLE buku (
    id    INTEGER PRIMARY KEY,
    judul TEXT NOT NULL,
    stok  INTEGER
);

INSERT INTO buku (judul, stok) VALUES ('Peta Langit', 4), ('Dasar Algoritma', 2);

ALTER TABLE buku ADD COLUMN tahun INTEGER NOT NULL;

ALTER TABLE buku DROP COLUMN id;

ALTER TABLE buku ALTER COLUMN judul TYPE TEXT;
text Keluaran 
Error near line 9: Cannot add a NOT NULL column with default value NULL
Parse error near line 11: cannot drop PRIMARY KEY column: "id"
Parse error near line 13: near "TYPE": syntax error
  ALTER TABLE buku ALTER COLUMN judul TYPE TEXT;
                        error here ---^

Pesan pertama muncul karena kolom NOT NULL tanpa nilai bawaan akan membuat baris lama langsung melanggar aturan yang baru dibuat. Pesan kedua menunjukkan bahwa kolom PRIMARY KEY dilindungi dari DROP COLUMN, sama seperti kolom yang terindeks atau dirujuk oleh constraint lain. Pesan ketiga bersifat lebih mendasar, yaitu ALTER COLUMN bukan bagian dari bahasa SQLite, sehingga parser menolak sintaksnya sebelum tabel tersentuh. Perubahan tipe kolom dengan demikian selalu memerlukan pendekatan lain, dan pendekatan itulah yang dibahas pada bagian berikutnya.

Ketiga penolakan tersebut bersifat konservatif, yaitu SQLite memilih menolak daripada menebak maksud perubahan yang diminta. Pendekatan ini membuat bentuk ALTER TABLE yang tersedia benar-benar dapat diandalkan, karena setiap bentuknya terdefinisi sempurna dan tidak berinteraksi dengan index maupun trigger secara diam-diam. Perubahan yang ditolak parser kemudian dialihkan ke prosedur manual pada bagian akhir topik ini, yang seluruh langkahnya dikontrol secara eksplisit.

Pola tabel baru dan penyalinan#

Untuk perubahan yang melampaui kemampuan ALTER TABLE, dokumentasi resmi merangkum prosedur dua belas langkah pada halaman ALTER TABLE. Prosedurnya adalah sebagai berikut.

  1. Matikan penegakan foreign key dengan PRAGMA foreign_keys = OFF.
  2. Mulai transaksi dengan BEGIN.
  3. Simpan definisi seluruh index, trigger, dan view yang terkait dengan tabel lama.
  4. Buat tabel baru dengan struktur yang diinginkan.
  5. Salin isi tabel lama ke tabel baru dengan INSERT ... SELECT.
  6. Hapus tabel lama dengan DROP TABLE.
  7. Buat kembali tabel lama dengan struktur yang baru.
  8. Salin isi tabel baru ke tabel yang dibuat kembali.
  9. Hapus tabel baru.
  10. Buat kembali index, trigger, dan view yang disimpan pada langkah ketiga.
  11. Periksa integritas referensi dengan PRAGMA foreign_key_check.
  12. Simpan transaksi dengan COMMIT.

Blok berikut menyederhanakan prosedur tersebut karena tabel buku pada contoh tidak memiliki index, trigger, maupun view. Perubahannya adalah mengganti tipe kolom tahun dari TEXT menjadi INTEGER, sesuatu yang tidak dapat dilakukan ALTER TABLE.

bash 
# pola tabel baru: ubah tipe kolom tahun dari TEXT menjadi INTEGER
rm -f perpustakaan.db
sqlite3 perpustakaan.db <<'SQL'
PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE buku (
    id    INTEGER PRIMARY KEY,
    judul TEXT,
    tahun TEXT
);
INSERT INTO buku VALUES (1, 'Dasar Algoritma', '2019'), (2, 'Peta Langit', '2021');
CREATE TABLE buku_baru (
    id    INTEGER PRIMARY KEY,
    judul TEXT NOT NULL,
    tahun INTEGER
);
INSERT INTO buku_baru SELECT id, judul, CAST(tahun AS INTEGER) FROM buku;
DROP TABLE buku;
ALTER TABLE buku_baru RENAME TO buku;
COMMIT;
PRAGMA foreign_keys = ON;
SELECT id, judul, tahun, typeof(tahun) FROM buku;
SQL
text Keluaran 
1|Dasar Algoritma|2019|integer
2|Peta Langit|2021|integer

Keluaran memperlihatkan tabel buku dengan struktur baru, dan typeof(tahun) mengonfirmasi bahwa nilai yang tersimpan kini berupa bilangan bulat hasil CAST. Pola yang sama dipakai untuk menambahkan constraint pada kolom lama, mengurutkan ulang kolom, maupun memisahkan satu tabel menjadi dua. Penyalinan dengan INSERT ... SELECT juga menjadi titik untuk membersihkan data, misalnya mengisi tahun yang kosong sebelum kolomnya diberi constraint NOT NULL.

Ringkasan#

  • ALTER TABLE menangani RENAME, ADD COLUMN, dan DROP COLUMN, tetapi tidak mengubah tipe kolom maupun constraint kolom lama.
  • ADD COLUMN dengan NOT NULL wajib menyertakan DEFAULT lain selain NULL apabila tabel sudah berisi baris.
  • Perubahan rumit mengikuti prosedur dua belas langkah, yang intinya membuat tabel baru, menyalin data, menghapus tabel lama, lalu mengganti nama tabel barunya.

Referensi#