KAL
Menu

Constraint

PRIMARY KEY, NOT NULL, UNIQUE, dan CHECK (MySQL 8.0.16+) sebagai penjaga integritas data.

Daftar isi

Data yang salah strukturnya jauh lebih sulit diperbaiki daripada data yang ditolak sejak awal, dan constraint adalah mekanisme MySQL untuk menolaknya. Constraint berupa aturan integritas yang dideklarasikan pada skema, lalu ditegakkan server terhadap setiap pernyataan INSERT, UPDATE, dan DELETE tanpa tergantung pada kerapian aplikasi. Aturan semacam ini, misalnya, menolak dua baris anggota yang menyimpan alamat email yang sama. Topik ini membahas empat constraint paling mendasar, yaitu PRIMARY KEY, NOT NULL, UNIQUE, dan CHECK, lengkap dengan demonstrasi pelanggaran masing-masing pada basis data perpustakaan.

PRIMARY KEY: identitas setiap baris#

PRIMARY KEY menetapkan kolom atau gabungan kolom yang mengidentifikasi setiap baris secara unik. Satu tabel hanya boleh memiliki satu primary key, dan kolomnya otomatis memperoleh sifat NOT NULL serta UNIQUE, sehingga tidak ada baris yang boleh kosong identitasnya. Server juga membangun indeks PRIMARY pada kolom tersebut, dan indeks inilah yang dipakai InnoDB untuk menata baris secara fisik. Berkas skema berikut dipakai sepanjang topik ini, dan setiap tabelnya menetapkan primary key pada kolom id.

sql skema-perpustakaan.sql 
-- tidak-dijalankan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'aktif',
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email),
  CONSTRAINT chk_status CHECK (status IN ('aktif', 'nonaktif'))
) ENGINE=InnoDB;

CREATE TABLE buku (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  kode_buku VARCHAR(12) NOT NULL,
  judul VARCHAR(200) NOT NULL,
  penulis VARCHAR(100) NOT NULL,
  tahun SMALLINT UNSIGNED,
  stok INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_kode (kode_buku),
  CONSTRAINT chk_stok CHECK (stok >= 0),
  CONSTRAINT chk_tahun CHECK (tahun IS NULL OR tahun BETWEEN 1500 AND 2100)
) ENGINE=InnoDB;
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < skema-perpustakaan.sql

Daftar indeks pada information_schema.statistics memperlihatkan akibat deklarasi tersebut. Setiap primary key dan unique key otomatis mendapat indeks pendukung, dan kolom NON_UNIQUE bernilai 0 menandakan indeksnya tidak menerima duplikasi.

bash 
# menelusuri indeks pendukung setiap constraint kunci
mysql -t -u root -e "SELECT table_name, index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema = 'perpustakaan' ORDER BY table_name, index_name, seq_in_index"
text Keluaran 
+------------+------------+-------------+------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | NON_UNIQUE |
+------------+------------+-------------+------------+
| anggota    | PRIMARY    | id          |          0 |
| anggota    | uq_email   | email       |          0 |
| buku       | PRIMARY    | id          |          0 |
| buku       | uq_kode    | kode_buku   |          0 |
+------------+------------+-------------+------------+

NOT NULL: kolom yang wajib terisi#

Atribut NOT NULL melarang nilai kosong pada kolomnya. Server memeriksanya pada dua keadaan, yaitu ketika pernyataan menyebut NULL secara eksplisit dan ketika pernyataan mengabaikan kolom yang tidak memiliki nilai bawaan. Kedua bentuk kegagalan berikut terjadi pada tabel anggota yang kolom nama-nya bertanda NOT NULL.

bash 
# menyisipkan NULL secara eksplisit pada kolom nama
mysql -u root perpustakaan -e "INSERT INTO anggota (id, nama) VALUES (1, NULL)"
text Keluaran 
ERROR 1048 (23000) at line 1: Column 'nama' cannot be null
bash 
# mengabaikan kolom nama yang tidak memiliki nilai bawaan
mysql -u root perpustakaan -e "INSERT INTO anggota (email) VALUES ('[email protected]')"
text Keluaran 
ERROR 1364 (HY000) at line 1: Field 'nama' doesn't have a default value

Error 1048 muncul untuk NULL yang ditulis langsung, sedangkan error 1364 muncul untuk kolom wajib yang tidak disebut sama sekali. Pesan keduanya menyebut nama kolom yang bermasalah, sehingga penyebabnya mudah dilacak dari log aplikasi. Deklarasikan NOT NULL pada setiap kolom yang wajib terisi dan berikan DEFAULT bila ada nilai yang wajar untuk keadaan kosong.

UNIQUE: mencegah duplikasi nilai#

UNIQUE KEY menjamin bahwa setiap nilai pada kolomnya muncul paling banyak satu kali di seluruh tabel. Constraint ini dipakai untuk kolom yang secara logika memang tidak boleh ganda, misalnya alamat email anggota atau kode katalog buku. Isi dua baris anggota terlebih dahulu, lalu coba menyisipkan baris ketiga dengan email yang sama.

bash 
# mengisi dua baris anggota sebagai data awal
mysql -u root perpustakaan <<'SQL'
INSERT INTO anggota (nama, email)
VALUES ('Ali', '[email protected]'), ('Budi', '[email protected]');
SQL
bash 
# menyisipkan anggota ketiga dengan email yang sudah dipakai
mysql -u root perpustakaan -e "INSERT INTO anggota (nama, email) VALUES ('Citra', '[email protected]')"
text Keluaran 
ERROR 1062 (23000) at line 1: Duplicate entry '[email protected]' for key 'anggota.uq_email'

Error 1062 menolak penyisipan dan menyebut nama constraint uq_email, nama yang memang diberikan secara eksplisit pada definisi tabel. Perlu dicatat bahwa kolom UNIQUE yang mengizinkan NULL boleh menyimpan lebih dari satu NULL, karena NULL tidak dianggap sama dengan nilai apa pun, termasuk NULL lain.

CHECK: aturan nilai yang dinyatakan sendiri#

Constraint CHECK menetapkan syarat yang harus dipenuhi setiap nilai kolom, dan mulai MySQL 8.0.16 syarat itu benar-benar ditegakkan. Constraint ini dipakai untuk aturan yang tidak tercakup oleh tipe data maupun kunci, misalnya daftar status yang sah atau rentang tahun terbit. Tabel skema di atas memuat tiga constraint CHECK yang semuanya diberi nama dengan awalan chk_.

Keluaran information_schema.check_constraints berikut menunjukkan bentuk normalisasi tiap syarat. Server menuliskan kembali teks aslinya secara kanonis, termasuk awalan charset pada literal string, sehingga bentuknya sedikit berbeda dari yang ditulis pada CREATE TABLE.

bash 
# menelusuri daftar constraint CHECK pada basis data perpustakaan
mysql -t -u root -e "SELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE constraint_schema = 'perpustakaan' ORDER BY constraint_name"
text Keluaran 
+-----------------+--------------------------------------------------------+
| CONSTRAINT_NAME | CHECK_CLAUSE                                           |
+-----------------+--------------------------------------------------------+
| chk_status      | (`status` in (_utf8mb4\'aktif\',_utf8mb4\'nonaktif\')) |
| chk_stok        | (`stok` >= 0)                                          |
| chk_tahun       | ((`tahun` is null) or (`tahun` between 1500 and 2100)) |
+-----------------+--------------------------------------------------------+

Sisipkan satu baris yang melanggar chk_status, lalu satu baris lagi yang melewati perbatasan tipe data pada kolom stok.

bash 
# menyisipkan status di luar daftar yang diizinkan
mysql -u root perpustakaan -e "INSERT INTO anggota (nama, email, status) VALUES ('Dewi', '[email protected]', 'pendanaan')"
text Keluaran 
ERROR 3819 (HY000) at line 1: Check constraint 'chk_status' is violated.
bash 
# menyisipkan stok negatif yang ditolak oleh tipe UNSIGNED
mysql -u root perpustakaan -e "INSERT INTO buku (kode_buku, judul, penulis, stok) VALUES ('ST-001', 'Basis Data Terapan', 'Citra Lestari', -1)"
text Keluaran 
ERROR 1264 (22003) at line 1: Out of range value for column 'stok' at row 1

Perhatikan bahwa percobaan kedua gagal dengan error 1264, bukan dengan pesan constraint. Kolom stok bertipe INT UNSIGNED sehingga angka negatif ditolak pada tipe datanya sebelum constraint CHECK sempat dievaluasi. Lapisan tipe dan lapisan constraint bekerja bersama, dan pelanggaran ditangkap oleh lapisan terluar lebih dahulu.

Constraint CHECK juga dapat dirancang agar menerima NULL melalui syarat tahun IS NULL OR .... Syarat seperti ini berguna untuk kolom opsional yang nilainya, apabila diisi, harus berada dalam rentang tertentu.

bash 
# mengisi buku tanpa tahun terbit, lalu menelusurinya
mysql -t -u root perpustakaan <<'SQL'
INSERT INTO buku (kode_buku, judul, penulis, tahun, stok)
VALUES ('AL-001', 'Algoritma Dasar', 'Rina Handayani', NULL, 3);
SELECT id, kode_buku, tahun, stok FROM buku ORDER BY id;
SQL
text Keluaran 
+----+-----------+-------+------+
| id | kode_buku | tahun | stok |
+----+-----------+-------+------+
|  1 | AL-001    |  NULL |    3 |
+----+-----------+-------+------+
bash 
# menyisipkan tahun terbit di luar rentang yang diizinkan
mysql -u root perpustakaan -e "INSERT INTO buku (kode_buku, judul, penulis, tahun) VALUES ('PM-002', 'Pemrograman Basis Data', 'Budi Santoso', 1200)"
text Keluaran 
ERROR 3819 (HY000) at line 1: Check constraint 'chk_tahun' is violated.

Kesalahan umum#

Kesalahan pertama adalah membiarkan constraint tanpa nama. Tanpa nama eksplisit, server menghasilkan nama bawaan yang tidak bermakna, dan pesan error maupun audit skema menjadi sulit dipetakan ke kolomnya. Beri nama yang konsisten, misalnya awalan uq_ untuk unique key dan awalan chk_ untuk constraint CHECK.

Kesalahan kedua adalah menumpuk perlindungan pada satu lapisan saja. Tipe UNSIGNED menolak angka negatif, CHECK menolak nilai di luar syarat, dan NOT NULL menolak kekosongan, sehingga ketiganya dirancang saling melengkapi. Aturan yang dapat ditegakkan oleh tipe data sebaiknya ditegakkan di sana, sedangkan CHECK disisakan untuk aturan logika yang lebih rumit.

Kesalahan ketiga adalah mengira UNIQUE hanya mengizinkan satu baris kosong. Pada kolom UNIQUE yang mengizinkan NULL, baris dengan NULL boleh bertumpuk tanpa batas. Apabila keunikan wajib mencakup kekosongan, tambahkan NOT NULL pada kolom tersebut agar kedua constraint bekerja bersama.

Ringkasan#

  • PRIMARY KEY mengidentifikasi setiap baris, otomatis memberi sifat NOT NULL dan UNIQUE, serta membangun indeks PRIMARY.
  • NOT NULL ditolak dengan error 1048 apabila disebut NULL dan dengan error 1364 apabila kolom wajib diabaikan tanpa nilai bawaan.
  • UNIQUE KEY menolak duplikasi dengan error 1062, tetapi tetap menerima banyak NULL pada kolom yang mengizinkannya.
  • Constraint CHECK yang ditegakkan sejak MySQL 8.0.16 memeriksa syarat yang dinyatakan sendiri dan dilaporkan dengan error 3819 ketika dilanggar.

Referensi#