Mengubah VIEW

View yang kita buat kemarin bisa saja tidak cocok lagi dengan kebutuhan hari ini: laporan butuh satu kolom tambahan, filter perlu diganti, atau nama kolom hasil harus lebih deskriptif. MySQL tidak menyediakan statement “perbarui sebagian definisi view” — mengubah view selalu berarti mengganti total query yang tersimpan di dalamnya. Kabar baiknya, ada dua cara resmi untuk itu: CREATE OR REPLACE VIEW dan ALTER VIEW. Chapter ini juga membahas sisi yang sering terlupakan: view merujuk kolom tabel dasar berdasarkan nama, sehingga ketika tabel dasar berubah struktur — kolom ditambah atau dihapus — view bisa diam-diam kehilangan kolom barunya atau rusak total sampai memunculkan ERROR 1356.

Menyiapkan Tabel dan View Latihan #

Kita pakai kembali database belajar_view dengan tabel karyawan delapan baris, lalu membuka view sederhana untuk pegawai IT sebagai bahan pengubahan:

sql
DROP DATABASE IF EXISTS belajar_view;
CREATE DATABASE belajar_view;
USE belajar_view;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE karyawan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  gaji DECIMAL(10,2) NOT NULL,
  departemen VARCHAR(30) NOT NULL,
  tanggal_masuk DATE NOT NULL
);

INSERT INTO karyawan (nama, gaji, departemen, tanggal_masuk) VALUES
  ('Budi Santoso',   8500000.00, 'IT',        '2021-03-01'),
  ('Siti Rahayu',    7200000.00, 'IT',        '2022-07-15'),
  ('Andi Wijaya',    9500000.00, 'Keuangan',  '2020-01-20'),
  ('Dewi Lestari',   6800000.00, 'Keuangan',  '2023-02-10'),
  ('Rina Kurnia',    5500000.00, 'HR',        '2023-09-05'),
  ('Tono Prasetyo',  7700000.00, 'IT',        '2021-11-01'),
  ('Sari Wulandari', 6300000.00, 'HR',        '2024-01-08'),
  ('Joko Susilo',    8900000.00, 'Pemasaran', '2022-04-12');

CREATE VIEW v_karyawan_it AS
SELECT id, nama, gaji, departemen FROM karyawan WHERE departemen = 'IT';

SELECT id, nama, gaji, departemen FROM v_karyawan_it ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+----+---------------+------------+------------+
| id | nama          | gaji       | departemen |
+----+---------------+------------+------------+
|  1 | Budi Santoso  | 8500000.00 | IT         |
|  2 | Siti Rahayu   | 7200000.00 | IT         |
|  6 | Tono Prasetyo | 7700000.00 | IT         |
+----+---------------+------------+------------+
3 rows in set (0.00 sec)

View awal ini memuat empat kolom — tanpa tanggal_masuk — dan menyaring tiga pegawai departemen IT. Kondisi inilah titik berangkat bagi seluruh pengubahan pada bagian berikutnya.

Mengganti Definisi dengan CREATE OR REPLACE VIEW #

Cara utama mengubah view adalah membuatnya ulang dengan CREATE OR REPLACE VIEW. Kata kunci OR REPLACE menginstruksikan server membuang definisi lama lalu menyimpan definisi baru pada satu nama yang sama. Skenarionya: tim HR kini ingin kolom tanggal_masuk ikut tampil pada view pegawai IT:

sql
CREATE OR REPLACE VIEW v_karyawan_it AS
SELECT id, nama, gaji, departemen, tanggal_masuk
FROM karyawan WHERE departemen = 'IT';

SELECT id, nama, gaji, departemen, tanggal_masuk FROM v_karyawan_it ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+----+---------------+------------+------------+---------------+
| id | nama          | gaji       | departemen | tanggal_masuk |
+----+---------------+------------+------------+---------------+
|  1 | Budi Santoso  | 8500000.00 | IT         | 2021-03-01    |
|  2 | Siti Rahayu   | 7200000.00 | IT         | 2022-07-15    |
|  6 | Tono Prasetyo | 7700000.00 | IT         | 2021-11-01    |
+----+---------------+------------+------------+---------------+
3 rows in set (0.00 sec)

Hasil pembacaan view kini memuat lima kolom. Mari kita bedah proses penggantiannya: statement CREATE OR REPLACE VIEW v_karyawan_it AS membuka objek v_karyawan_it yang sudah ada, menghapus query definisi lamanya, lalu menyimpan query baru SELECT id, nama, gaji, departemen, tanggal_masuk FROM karyawan WHERE departemen = 'IT' sebagai satu-satunya definisi. Tidak ada mekanisme “tambahkan satu kolom pada view” — daftar kolom lama dibuang seluruhnya dan digantikan daftar kolom yang kita tulis pada statement pengganti. Oleh sebab itu statement ini juga tepat dipakai ketika klausa WHERE, alias, atau urutan kolom perlu dirombak: satu statement menuntaskan semuanya.

Alternatif Sintaksis: ALTER VIEW #

MySQL juga menyediakan ALTER VIEW yang kegunaannya setara: mengganti definisi view yang sudah ada. Skenario berikutnya: laporan pihak ketiga hanya boleh menerima nama pegawai dan gajinya, dan kolom gaji diminta tampil dengan nama gaji_bulanan:

sql
ALTER VIEW v_karyawan_it AS
SELECT id, nama, gaji AS gaji_bulanan FROM karyawan WHERE departemen = 'IT';

SELECT id, nama, gaji_bulanan FROM v_karyawan_it ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+----+---------------+--------------+
| id | nama          | gaji_bulanan |
+----+---------------+--------------+
|  1 | Budi Santoso  |   8500000.00 |
|  2 | Siti Rahayu   |   7200000.00 |
|  6 | Tono Prasetyo |   7700000.00 |
+----+---------------+--------------+
3 rows in set (0.00 sec)

Hasilnya identik secara semantik dengan CREATE OR REPLACE VIEW yang memuat query yang sama: tiga kolom tampil, kolom gaji kini bernama gaji_bulanan. Perbedaan kedua statement ini terletak pada syarat awalnya: CREATE OR REPLACE VIEW akan membuat view baru bila namanya belum dipakai, sedangkan ALTER VIEW menuntut view sudah ada. Permintaan ALTER VIEW untuk nama yang tidak dikenal ditolak server:

sql
-- @error
ALTER VIEW v_belum_ada AS SELECT id FROM karyawan;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1146 (42S02): Table 'belajar_view.v_belum_ada' doesn't exist
Bye

Server menjawab ERROR 1146 (42S02): Table 'belajar_view.v_belum_ada' doesn't exist karena ALTER VIEW tidak mempunyai jalur pembuatan objek. Dalam praktik, tim SQL kerap memakai CREATE OR REPLACE VIEW saja supaya satu template statement berlaku baik untuk view baru maupun view lama.

Ketika Tabel Dasar Berubah Struktur #

Pengubahan view yang paling berbahaya justru tidak terjadi pada view-nya, melainkan pada tabel dasarnya. Untuk mengamatinya, kita buat dulu view v_semua yang memakai SELECT *:

sql
CREATE VIEW v_semua AS SELECT * FROM karyawan;

SELECT * FROM v_semua ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+----+----------------+------------+------------+---------------+
| id | nama           | gaji       | departemen | tanggal_masuk |
+----+----------------+------------+------------+---------------+
|  1 | Budi Santoso   | 8500000.00 | IT         | 2021-03-01    |
|  2 | Siti Rahayu    | 7200000.00 | IT         | 2022-07-15    |
|  3 | Andi Wijaya    | 9500000.00 | Keuangan   | 2020-01-20    |
|  4 | Dewi Lestari   | 6800000.00 | Keuangan   | 2023-02-10    |
|  5 | Rina Kurnia    | 5500000.00 | HR         | 2023-09-05    |
|  6 | Tono Prasetyo  | 7700000.00 | IT         | 2021-11-01    |
|  7 | Sari Wulandari | 6300000.00 | HR         | 2024-01-08    |
|  8 | Joko Susilo    | 8900000.00 | Pemasaran  | 2022-04-12    |
+----+----------------+------------+------------+---------------+
8 rows in set (0.00 sec)

Sebelum mengubah struktur karyawan, langkah aman pertama adalah mengaudit view mana saja yang merujuk tabel tersebut. Kolom VIEW_DEFINITION pada information_schema.VIEWS menyimpan definisi ternormalisasi setiap view, sehingga pencarian teks sederhana sudah cukup untuk audit:

sql
SELECT TABLE_NAME FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'belajar_view' AND VIEW_DEFINITION LIKE '%`karyawan`%'
ORDER BY TABLE_NAME;

Output yang Dihasilkan:

text
+---------------+
| TABLE_NAME    |
+---------------+
| v_karyawan_it |
| v_semua       |
+---------------+
2 rows in set (0.00 sec)

Hasil audit menunjukkan dua view bergantung pada karyawan: v_karyawan_it dan v_semua. Sekarang kita tambahkan kolom email ke tabel dasar, lalu membaca ulang kedua sisi:

sql
ALTER TABLE karyawan ADD COLUMN email VARCHAR(100) NULL DEFAULT NULL;

SELECT * FROM v_semua ORDER BY id;

SELECT id, nama, email FROM karyawan ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----+----------------+------------+------------+---------------+
| id | nama           | gaji       | departemen | tanggal_masuk |
+----+----------------+------------+------------+---------------+
|  1 | Budi Santoso   | 8500000.00 | IT         | 2021-03-01    |
|  2 | Siti Rahayu    | 7200000.00 | IT         | 2022-07-15    |
|  3 | Andi Wijaya    | 9500000.00 | Keuangan   | 2020-01-20    |
|  4 | Dewi Lestari   | 6800000.00 | Keuangan   | 2023-02-10    |
|  5 | Rina Kurnia    | 5500000.00 | HR         | 2023-09-05    |
|  6 | Tono Prasetyo  | 7700000.00 | IT         | 2021-11-01    |
|  7 | Sari Wulandari | 6300000.00 | HR         | 2024-01-08    |
|  8 | Joko Susilo    | 8900000.00 | Pemasaran  | 2022-04-12    |
+----+----------------+------------+------------+---------------+
8 rows in set (0.00 sec)

+----+----------------+-------+
| id | nama           | email |
+----+----------------+-------+
|  1 | Budi Santoso   | NULL  |
|  2 | Siti Rahayu    | NULL  |
|  3 | Andi Wijaya    | NULL  |
|  4 | Dewi Lestari   | NULL  |
|  5 | Rina Kurnia    | NULL  |
|  6 | Tono Prasetyo  | NULL  |
|  7 | Sari Wulandari | NULL  |
|  8 | Joko Susilo    | NULL  |
+----+----------------+-------+
8 rows in set (0.00 sec)

Hasilnya memperlihatkan perilaku yang mengejutkan pemula: query langsung ke karyawan menampilkan kolom email yang baru, tetapi SELECT * FROM v_semua tetap memperlihatkan lima kolom lama. Penyebabnya, SELECT * pada definisi view dievaluasi tepat satu kali — saat statement CREATE VIEW dijalankan — dan hasil evaluasinya itulah (daftar kolom saat itu) yang disimpan. Kolom yang ditambahkan setelahnya tidak masuk ke daftar. Menambahkan kolom tidak merusak apa pun; justru menghapus kolom yang dirujuk view yang berakibat fatal:

sql
ALTER TABLE karyawan DROP COLUMN gaji;

Output yang Dihasilkan:

text

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Statement penghapusan kolom gaji sendiri sukses tanpa keluhan — MySQL tidak memeriksa kondisi view pada saat ALTER TABLE. Kerusakan baru terlihat ketika view yang merujuk kolom itu digunakan:

sql
-- @error
SELECT * FROM v_semua;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1356 (HY000): View 'belajar_view.v_semua' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Bye

Server menolak dengan ERROR 1356 (HY000): View 'belajar_view.v_semua' references invalid table(s) or column(s)... — definisi view menyimpan daftar kolom id, nama, gaji, departemen, tanggal_masuk, dan kolom gaji sudah tidak lagi ada di karyawan. Perhatikan pula bahwa v_karyawan_it berada pada kondisi rusak yang sama karena definisinya juga merujuk kolom gaji. View yang rusak dapat diperbaiki dengan menuliskan ulang definisinya memakai kolom yang tersisa:

sql
CREATE OR REPLACE VIEW v_semua AS
SELECT id, nama, departemen, tanggal_masuk, email FROM karyawan;

SELECT * FROM v_semua ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+----+----------------+------------+---------------+-------+
| id | nama           | departemen | tanggal_masuk | email |
+----+----------------+------------+---------------+-------+
|  1 | Budi Santoso   | IT         | 2021-03-01    | NULL  |
|  2 | Siti Rahayu    | IT         | 2022-07-15    | NULL  |
|  3 | Andi Wijaya    | Keuangan   | 2020-01-20    | NULL  |
|  4 | Dewi Lestari   | Keuangan   | 2023-02-10    | NULL  |
|  5 | Rina Kurnia    | HR         | 2023-09-05    | NULL  |
|  6 | Tono Prasetyo  | IT         | 2021-11-01    | NULL  |
|  7 | Sari Wulandari | HR         | 2024-01-08    | NULL  |
|  8 | Joko Susilo    | Pemasaran  | 2022-04-12    | NULL  |
+----+----------------+------------+---------------+-------+
8 rows in set (0.00 sec)

Definisi baru v_semua hanya memuat kolom yang masih ada, sehingga pembacaan view kembali normal — kini dengan lima kolom hasil susunan ulang kita. Kolom gaji hilang dari daftar dan susunan kolom baru sepenuhnya mengikuti statement pengganti, bukan hasil menambal definisi lama sepotong-sepotong.

Urutan Aman Ketika Skema Berubah #

Rangkaian kejadian di atas bisa kita rangkum menjadi prosedur aman saat tabel dasar berubah struktur. Pertama, audit dependensi dengan query information_schema.VIEWS seperti di atas supaya tidak ada view yang terlupakan. Kedua, putuskan strategi: bila perubahan hanya menambah kolom, view lama tetap berjalan (kolom baru memang tidak tampil, dan itu sering diinginkan); bila perubahan menghapus atau mengganti nama kolom yang dirujuk, siapkan definisi baru setiap view terdampak. Ketiga, terapkan ALTER TABLE, lalu segera perbaiki view dengan CREATE OR REPLACE VIEW — dan uji setiap view dengan satu SELECT sebelum dianggap selesai.

Membersihkan Database Latihan #

Latihan pengubahan selesai dan database latihan kita jatuhkan:

sql
DROP DATABASE belajar_view;

Output yang Dihasilkan:

text

Query OK, 3 rows affected (0.00 sec)

Referensi #

Definisi view kini bisa kita ganti total lewat dua pintu — CREATE OR REPLACE VIEW dan ALTER VIEW — lengkap dengan pemahaman risiko ketika skema tabel dasar berubah. Sisi kehidupan view yang belum kita bahas adalah akhirnya: view yang sudah tidak terpakai perlu dihapus secara eksplisit, dan penghapusannya punya jebakan tersendiri ketika satu view dibangun di atas view lain. Chapter berikutnya membahas DROP VIEW beserta pola-pola amannya.