Menambah/Mengubah Batasan dan Redefinisi Tabel

Data yang akan kita pakai — legacy_user

Tabel di produksi sering kali ternyata membutuhkan batasan yang tidak disertakan di desain awal.

Hal-hal seperti "lekatkan nilai default ke kolom ini," "jadikan email bebas duplikat (UNIQUE)," atau "tandai field wajib ini sebagai NOT NULL."

Artikel ini membahas penambahan kolom dan retrofit nilai default, serta prosedur rebuild tabel untuk menambahkan batasan ke tabel yang sudah ada.

Sebelum latihan, lihat definisi kolom dan data sampel dari tabel legacy_user.

① Jalankan PRAGMA table_info(legacy_user); untuk memeriksa nama kolom, tipe, primary key, NOT NULL, dan nilai default.

② Jalankan SELECT * FROM legacy_user; untuk pratinjau semua baris.

ALTER TABLE ADD COLUMN — retrofit kolom dan nilai default

Untuk menambah kolom baru ke tabel yang sudah ada, gunakan ALTER TABLE table ADD COLUMN nama_kolom tipe batasan.

Melekatkan DEFAULT value menetapkan nilai default yang dipakai ketika kolom dikosongkan, dan saat ALTER nilai itu juga mengisi setiap baris yang sudah ada.

ADD COLUMN bahkan menerima NOT NULL ketika dikombinasikan dengan DEFAULT (baris yang ada diisi dengan default, jadi mereka tidak melanggar NOT NULL).

Sebaliknya, ADD COLUMN tidak bisa langsung melekatkan UNIQUE atau NOT NULL tanpa default.

Itu memerlukan "rebuild tabel".

-- Tambah kolom dengan DEFAULT (pakai tabel sekali pakai untuk melihat perilakunya)
DROP TABLE IF EXISTS alter_demo;
CREATE TABLE alter_demo(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO alter_demo VALUES (1,'Alice'),(2,'Bob');

-- Tambah created_on dengan DEFAULT → baris yang ada juga dapat nilainya
ALTER TABLE alter_demo ADD COLUMN created_on TEXT DEFAULT '2024-01-01';

-- NOT NULL juga bisa ketika dikombinasikan dengan DEFAULT
ALTER TABLE alter_demo ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM alter_demo ORDER BY id;

Bayangkan kebutuhannya: "tambahkan kolom status ke tabel user dan perlakukan semua user yang ada sebagai active." (Jalankan dengan benar dan penjelasannya muncul.)

① Tambahkan kolom TEXT bernama status ke legacy_user dengan nilai default 'active'.

② Lalu jalankan SELECT * FROM legacy_user; dan konfirmasi bahwa kolom status pada 2 baris yang ada terisi dengan nilai default.

Menambahkan batasan setelahnya berarti membangun ulang tabel

Di MySQL kamu bisa melekatkan batasan langsung ke tabel yang sudah ada, seperti ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);.

Di konsol kursus ini, me-retrofit UNIQUE atau NOT NULL ke tabel yang sudah ada melalui prosedur rebuild tabel: "buat tabel baru dengan batasan yang kamu inginkan, salin data yang ada, dan gantikan tabel lama."

MySQL / PostgreSQL juga melakukan pola rebuild ini secara internal untuk beberapa operasi (seperti perubahan tipe kolom), bergantung pada tool-nya.

Prosedurnya mengikuti gambar di bawah: matikan pemeriksaan foreign-key, lalu CREATE tabel baru → salin semua baris → DROP tabel lama → RENAME tabel baru → kembalikan pemeriksaannya.

Tentang ADD CONSTRAINT / AUTO_INCREMENT / komentar kolom MySQL

Sintaks ALTER TABLE ... ADD CONSTRAINT, AUTO_INCREMENT, dan komentar kolom (COMMENT '...') yang ditampilkan di bawah adalah sintaks MySQL.

Konsol browser di kursus ini tidak bisa menjalankannya, jadi kedua blok code ini hanya dibaca (jangan coba dieksekusi).

Di konsol kursus ini, retrofit batasan via rebuild tabel di bagian di bawah, dan pakai INTEGER PRIMARY KEY untuk auto-numbering.

-- Sintaks MySQL (jangan dijalankan di konsol kursus — cukup baca)

-- MySQL: lekatkan batasan UNIQUE langsung ke tabel yang sudah ada
ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);

-- MySQL: ubah menjadi NOT NULL via MODIFY
ALTER TABLE legacy_user MODIFY email VARCHAR(255) NOT NULL;

-- MySQL: auto-numbering adalah atribut kolom AUTO_INCREMENT; deskripsi adalah komentar kolom
CREATE TABLE member (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE COMMENT 'login email'
);

-- Di konsol kursus ini, dapatkan setara AUTO_INCREMENT via INTEGER PRIMARY KEY
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Kerangka rebuild tabel (pakai tabel sekali pakai rebuild_demo untuk melihat perilakunya)
DROP TABLE IF EXISTS rebuild_demo;
CREATE TABLE rebuild_demo(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO rebuild_demo VALUES (1,'a@example.com'),(2,'b@example.com');

-- ① Matikan pemeriksaan foreign-key
PRAGMA foreign_keys=OFF;

-- ② Tabel baru dengan batasan yang diinginkan (email NOT NULL UNIQUE)
CREATE TABLE rebuild_demo_new(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- ③ Salin data yang ada
INSERT INTO rebuild_demo_new(id, email)
SELECT id, email FROM rebuild_demo;

-- ④ Drop tabel lama, ⑤ rename tabel baru ke nama lama
DROP TABLE rebuild_demo;
ALTER TABLE rebuild_demo_new RENAME TO rebuild_demo;

-- ⑥ Kembalikan pemeriksaan foreign-key
PRAGMA foreign_keys=ON;

-- Konfirmasi batasan terpasang (email duplikat sekarang akan error)
SELECT sql FROM sqlite_master WHERE name='rebuild_demo';

Bayangkan kebutuhannya: "ubah kolom email di legacy_user menjadi wajib dan bebas duplikat (NOT NULL dan UNIQUE)." Jalankan prosedur rebuild tabel utama artikel, langkah demi langkah, persis seperti yang ditunjukkan di diagram atas.

① Matikan pemeriksaan foreign-key (PRAGMA foreign_keys=OFF;).

② CREATE tabel baru bernama legacy_user_new dengan id (primary key INTEGER), name (TEXT), dan email (TEXT, NOT NULL, UNIQUE).

③ Salin setiap baris dari tabel yang ada dengan INSERT INTO legacy_user_new(...) SELECT ... FROM legacy_user;.

④ DROP legacy_user lama dan RENAME legacy_user_new menjadi legacy_user.

⑤ Kembalikan pemeriksaan foreign-key ke ON.

⑥ Terakhir, jalankan SELECT sql FROM sqlite_master WHERE name='legacy_user'; dan konfirmasi schema baru berisi UNIQUE / NOT NULL.

Reproduksi rebuild yang sama pada tabel sekali pakai dan rasakan batasan UNIQUE schema baru benar-benar memblokir duplikat. Latihan ini mandiri dan memakai tabel khusus email_check.

① DROP TABLE IF EXISTS email_check; lalu CREATE dengan id (primary key INTEGER) dan email (TEXT, NOT NULL, UNIQUE).

② INSERT satu baris (1, 'a@example.com') ke email_check.

③ Lalu INSERT baris lain (2, 'a@example.com') yang nilai email-nya sudah ada. Ia melanggar batasan UNIQUE, jadi error adalah hasil yang benar untuk INSERT kedua.