SQL ON DELETE RESTRICT: Mencegah Kehilangan Data yang Tidak Disengaja

Menghapus data di SQL biasanya tidak sesederhana menghapus satu baris. Dalam basis data relasional, tabel sering saling terhubung, dan menghapus sebuah catatan pada satu tabel dapat berdampak pada tabel lain. 

SQL menyediakan klausa ON DELETE, yang mendefinisikan apa yang harus terjadi saat baris yang direferensikan dihapus. Setelah klausa ON DELETE, Anda dapat menentukan tindakan yang terjadi, seperti memblokir penghapusan, secara otomatis menghapus baris yang bergantung, atau memperbarui data yang direferensikan.

Dalam tutorial ini, saya akan menunjukkan kendala ON DELETE RESTRICT, yang mencegah penghapusan baris induk jika masih ada baris anak yang bergantung. Saya juga akan membahas secara mendalam bagaimana kendala ini bekerja, mengapa basis data menegakkannya, dan kapan ini menjadi pilihan yang tepat dibandingkan aturan penghapusan lainnya. Terakhir, jika Anda mencari perilaku yang berlawanan — di mana menghapus baris induk secara otomatis menghapus catatan terkait — lihat panduan kami tentang SQL ON DELETE CASCADE.

Apa Itu ON DELETE RESTRICT dalam SQL?

Kendala ON DELETE RESTRICT dalam SQL adalah fitur pengaman basis data. Ini memastikan bahwa Anda tidak dapat menghapus catatan "induk" jika masih ada catatan "anak" yang merujuk padanya.

Catatan: Meskipun basis data seperti PostgreSQL dan MySQL mendukung kata kunci RESTRICT secara langsung, SQL Server menerapkan perilaku pembatasan yang sama dengan menggunakan ON DELETE NO ACTION.

Integritas referensial dan foreign key

Integritas referensial adalah prinsip bahwa hubungan antar tabel harus tetap valid setiap saat. Foreign key menegakkan hal ini dengan mewajibkan nilai di tabel anak sesuai dengan baris yang ada di tabel induk. Sebagai contoh, DepartmentID di tabel Employees mungkin mereferensikan ID tertentu di tabel Departments.

Oleh karena itu, aturan DELETE di SQL ada karena menghapus baris induk dapat merusak hubungan ini. Basis data memerlukan instruksi yang jelas apakah harus memblokir penghapusan, melakukan cascade, atau menerapkan tindakan lain pada baris yang bergantung.

Apa arti RESTRICT sebenarnya

Saat Anda mendefinisikan foreign key dengan ON DELETE RESTRICT, basis data memeriksa baris anak yang terkait sebelum menjalankan penghapusan. Jika ada catatan anak, operasi penghapusan akan ditolak secara otomatis. Dalam hal ini, RESTRICT menghentikan penghapusan agar tidak terjadi alih-alih memodifikasi data, sebagaimana akan kita lihat nanti di tutorial.

Cara Kerja ON DELETE RESTRICT dalam SQL

Untuk memahami bagaimana kendala ON DELETE RESTRICT bekerja, Anda perlu melihat bagaimana basis data mengevaluasi penghapusan langkah demi langkah selama sebuah transaksi.

Hubungan tabel induk dan anak

Dalam SQL, tabel induk menyimpan catatan utama, sementara tabel anak menyimpan catatan yang bergantung. Misalnya, basis data Anda mungkin memiliki tabel induk Categories dengan category_id “Electronics.” Tabel anak Products akan memiliki catatan “Smartphone”, lalu ditautkan ke tabel induk menggunakan category_id.

Saat Anda menjalankan perintah DELETE pada tabel induk, mesin basis data segera memindai semua tabel anak yang terkait untuk melihat apakah ada baris yang menunjuk ke ID induk tersebut.

Apa yang terjadi saat Anda mencoba menghapus baris yang dibatasi?

Jika Anda mencoba menghapus baris induk yang masih direferensikan, basis data menghentikan operasi dan menampilkan error. Pesan error ini menunjukkan pelanggaran kendala foreign key.

Perilaku ini disengaja, memaksa Anda mengambil keputusan sadar untuk menghapus atau memperbarui baris anak yang bergantung terlebih dahulu, atau mempertimbangkan kembali apakah baris induk sebaiknya dihapus sama sekali.

SQL ON DELETE RESTRICT vs. Aturan Penghapusan Lainnya

Dalam sebuah DBMS, terdapat aturan spesifik yang mengatur bagaimana data dapat dihapus dari tabel yang saling terkait. Pada bagian ini, kita akan membandingkan kendala ON DELETE RESTRICT dengan aturan penghapusan umum di SQL, sehingga Anda dapat melihat dengan jelas bagaimana masing-masing memengaruhi data terkait.

RESTRICT vs. CASCADE dalam SQL

Kendala RESTRICT di SQL memblokir penghapusan baris induk jika baris anak mereferensikannya, memastikan tidak terjadi kehilangan data secara tidak sengaja. Kueri berikut menunjukkan cara menerapkan RESTRICT saat mendefinisikan skema tabel. Ini mencegah penghapusan pelanggan jika ada pesanan yang mereferensikan pelanggan tersebut.

-- Parent table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Child table with ON DELETE RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);Didukung oleh

Namun, kendala CASCADE di SQL memastikan bahwa sebuah penghapusan secara otomatis menghapus semua baris anak yang bergantung. Pada contoh di bawah, CASCADE memungkinkan penghapusan otomatis semua pesanan yang terkait dengan pelanggan ketika catatan pelanggan dihapus.

-- Child table with ON DELETE CASCADE
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);Didukung oleh

RESTRICT vs. SET NULL dan SET DEFAULT

Kendala SET NULL dan SET DEFAULT di SQL mempertahankan baris anak namun mengubah cara mereka mereferensikan induk.

Misalnya, pada kueri di bawah ini, ketika seorang pelanggan dihapus, customer_id pada pesanan terkait disetel menjadi NULL, menjaga catatan pesanan tetapi menghapus tautannya ke pelanggan.

-- Child table using SET NULL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);Didukung oleh

Jika Anda menggunakan kendala SET DEFAULT, ketika pelanggan dihapus, customer_id pada pesanan terkait disetel ke nilai defaultnya, mempertahankan catatan pesanan sambil menetapkan nilai placeholder yang telah ditentukan.

-- Child table using SET DEFAULT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT DEFAULT 0,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET DEFAULT
);Didukung oleh

Membuat Foreign Key dengan ON DELETE RESTRICT dalam SQL

Untuk menerapkan kendala ON DELETE RESTRICT di SQL, Anda perlu mendefinisikannya saat membuat hubungan antara dua tabel.

Mendefinisikan RESTRICT dalam skema tabel

Anda dapat menentukan perilaku penghapusan di SQL saat membuat tabel atau menambahkannya setelahnya jika tabel Anda sudah ada.

Contoh di bawah menunjukkan cara menggunakan kendala foreign key ON DELETE RESTRICT di SQL dalam pernyataan CREATE TABLE saat mendefinisikan tabel baru di SQL.

-- Stores department details
CREATE TABLE departments (
    department_id INT PRIMARY KEY, 
    department_name VARCHAR(100)
);

-- -- Prevents deleting a department with employees
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,      -- Unique employee identifier
    department_id INT, 
    CONSTRAINT fk_employees_departments
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT 
);Didukung oleh

Kueri di bawah menambahkan aturan foreign key yang sama ke tabel employees yang sudah ada dalam pernyataan ALTER TABLE di SQL, untuk mencegah penghapusan departemen yang masih direferensikan oleh karyawan.

-- Adds a foreign key constraint after the table already exists
ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE RESTRICT;    -- Blocks deletion of departments that have employeesDidukung oleh

Perilaku penghapusan default di SQL

Di banyak sistem basis data populer, seperti SQL Server, PostgreSQL, dan MySQL, RESTRICT adalah perilaku default. Jika Anda mendefinisikan foreign key tanpa menentukan klausa ON DELETE apa pun, basis data biasanya akan bertindak seolah-olah Anda menulis ON DELETE NO ACTION atau ON DELETE RESTRICT. Desain mesin basis data ini memastikan Anda menerima error daripada secara tidak sengaja menghapus sejumlah besar data terkait.

Kasus Penggunaan Umum untuk ON DELETE RESTRICT dalam SQL

Dalam skenario dunia nyata, kendala ON DELETE RESTRICT di SQL diterapkan ketika integritas data menuntut pengawasan eksplisit sebelum penghapusan.

Melindungi data referensi kritis

Kendala RESTRICT di SQL umum digunakan untuk tabel yang merepresentasikan data referensi inti, seperti pengguna, akun, produk, atau nilai lookup. Catatan-catatan ini sering digunakan di berbagai bagian sistem, dan menghapusnya secara otomatis dapat menyebabkan kehilangan data yang meluas atau inkonsistensi.

Misalnya, menghapus pengguna yang masih memiliki pesanan, log audit, atau izin biasanya tidak aman. Dengan kendala ON DELETE RESTRICT di SQL, basis data memblokir penghapusan dan memaksa Anda menyelesaikan dependensi tersebut terlebih dahulu, menjaga integritas data historis dan transaksional.

Menegakkan alur kerja pembersihan yang eksplisit

Kendala RESTRICT di SQL juga berguna saat Anda menginginkan pembersihan yang disengaja dan dapat ditelusuri. Ini memaksa pengembang dan administrator untuk lebih berhati-hati dalam menangani data. Alih-alih membiarkan basis data secara otomatis menghapus catatan, kendala RESTRICT memaksa Anda meninjau data yang bergantung untuk memutuskan apakah akan menghapus baris anak, menugaskannya kembali ke induk baru, atau membatalkan operasi.

Memecahkan Masalah Error DELETE yang Disebabkan oleh RESTRICT di SQL

Saat kendala ON DELETE RESTRICT memblokir penghapusan, error yang dihasilkan bisa membingungkan jika Anda tidak mengantisipasinya. Memahami error ini membantu Anda menyelesaikannya dengan aman.

Memahami error pelanggaran kendala

Saat Anda mencoba menghapus baris induk yang masih direferensikan, basis data menimbulkan pelanggaran kendala foreign key. Berikut adalah bagaimana pesan tersebut akan muncul di berbagai sistem basis data:

• PostgreSQL: ERROR: update or delete on table "parent_table" violates foreign key constraint "fk_name" on table "child_table"

• MySQL: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails…

• SQL Server: The DELETE statement conflicted with the REFERENCE constraint "FK_Name". The conflict occurred in database "DBName", table "dbo.ChildTable", column 'ParentID'.

Di semua basis data, pesan error akan menunjukkan hal-hal berikut:

• Kendala foreign key mana yang dilanggar
• Tabel mana yang bertindak sebagai anak
• Nilai kunci mana yang masih direferensikan

Menyelesaikan penghapusan yang terblokir secara aman

Jika Anda mendapatkan pesan error penghapusan yang terblokir, namun Anda memang ingin menghapus baris induk, saya merekomendasikan salah satu opsi berikut:

Hapus catatan anak terlebih dahulu: Jika data yang bergantung sudah tidak diperlukan, jalankan pernyataan DELETE pada baris anak terlebih dahulu, kemudian hapus catatan induk.

-- Step 1: Remove dependencies
DELETE FROM Orders WHERE CustomerID = 501; 
-- Step 2: Now the parent delete will work
DELETE FROM Customers WHERE CustomerID = 501;Didukung oleh

Perbarui referensi anak: Jika Anda ingin mempertahankan baris anak tetapi menghapus induknya, perbarui baris anak untuk mereferensikan ID induk yang berbeda.

-- Reassign orders to a "General" customer account (ID 999)
UPDATE Orders SET CustomerID = 999 WHERE CustomerID = 501;Didukung oleh

Ubah kendala: Dalam skenario administratif yang jarang terjadi, Anda dapat menonaktifkan pemeriksaan foreign key untuk sementara. Ini adalah operasi berisiko yang dapat menyebabkan kerusakan data jika tidak ditangani dengan sangat hati-hati.

Catatan dan Perilaku Spesifik Basis Data

Meskipun konsep kendala ON DELETE RESTRICT di SQL bersifat standar di seluruh basis data SQL, mesin yang berbeda menangani waktu dan logika internalnya sedikit berbeda.

Perilaku PostgreSQL, MySQL, dan SQL Server

Di PostgreSQL, MySQL, dan SQL Server, kendala ON DELETE RESTRICT mencegah penghapusan baris induk saat baris anak yang terkait ada. Namun, mesin-mesin ini memiliki perbedaan sintaks kecil berikut:

MySQL: Secara ketat mengikuti kendala RESTRICT di SQL. Jika Anda tidak menentukan aturan, default-nya adalah RESTRICT.

-- MySQL: RESTRICT is the default if no rule is specified
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);Didukung oleh

PostgreSQL: Mendukung kendala RESTRICT dan NO ACTION. Meskipun perilakunya hampir identik, RESTRICT bersifat benar-benar segera, sedangkan NO ACTION memungkinkan pemeriksaan yang ditangguhkan.

-- PostgreSQL: NO ACTION (can be deferred)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE NO ACTION
);Didukung oleh

SQL Server: Menggunakan kendala NO ACTION sebagai padanan perilaku standar RESTRICT. T-SQL tidak menggunakan kata kunci RESTRICT secara eksplisit, tetapi mencapai hasil yang sama dengan mencegah penghapusan.

-- SQL Server: Uses NO ACTION instead of RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE NO ACTION
);Didukung oleh

Saya merekomendasikan mengikuti kursus Introduction to SQL Server dan Creating PostgreSQL Databases untuk mempelajari lebih lanjut perbedaan dialek SQL saat membuat basis data.

Kendala tertunda dan perilaku transaksi

Dalam konfigurasi basis data lanjut, seperti di PostgreSQL atau Oracle, Anda mungkin menemui kendala yang ditangguhkan (deferred). Artinya kendala diperiksa saat komit transaksi, bukan segera ketika pernyataan DELETE dieksekusi.

Dalam sistem seperti ini, penghapusan yang biasanya akan diblokir oleh RESTRICT mungkin tampak berhasil sementara di dalam sebuah transaksi, namun gagal saat Anda mencoba melakukan commit. Ini memungkinkan operasi multi-langkah yang kompleks, seperti menghapus dan menyisipkan kembali data terkait dalam transaksi yang sama, sambil tetap menegakkan integritas di akhir.

Kursus Introduction to Oracle SQL kami akan membantu Anda lebih memahami bagaimana Oracle memroses kueri.

Memilih Strategi ON DELETE yang Tepat

Memilih kendala yang tepat membantu Anda menyeimbangkan antara integritas data dan kegunaan aplikasi. Pertimbangkan skenario berikut untuk membantu Anda memilih metode yang sesuai dengan klausa ON DELETE.

Kapan RESTRICT adalah pilihan paling aman

Gunakan kendala ON DELETE RESTRICT di SQL ketika:

• Data sangat berharga: Catatan induk penting dan banyak direferensikan dalam basis data.

• Diperlukan peninjauan manual: Anda ingin memaksa manusia atau alur logika bisnis tertentu untuk memutuskan apa yang terjadi pada data yang bergantung.

• Mencegah penghapusan “diam-diam”: Anda ingin memastikan pengembang tidak secara tidak sengaja menghapus ribuan baris dengan satu penghapusan CASCADE.

Saya menyarankan Anda mencoba SQL Server for Database Administrators skill track jika Anda secara rutin merancang atau memelihara basis data sebagai bagian dari pekerjaan Anda. 

Kapan mempertimbangkan alternatif

Jika kendala RESTRICT terasa terlalu kaku untuk sistem Anda, pertimbangkan opsi berikut:

• Gunakan kendala CASCADE saat catatan anak tidak memiliki makna tanpa catatan induk.

• Gunakan kendala SET NULL saat hubungan bersifat opsional.

• Gunakan kendala SET DEFAULT saat Anda memiliki “placeholder” yang seharusnya mewarisi semua catatan yatim piatu agar sistem tetap berjalan lancar.

Kesimpulan

Kendala ON DELETE RESTRICT di SQL adalah aturan foreign key yang protektif yang memprioritaskan integritas data dibanding kemudahan dengan memblokir penghapusan baris induk saat catatan anak yang bergantung masih ada. Dengan mendorong strategi pembersihan yang disengaja dan kepemilikan yang jelas atas operasi penghapusan, kendala RESTRICT di SQL bertindak sebagai fitur keselamatan dalam desain relasional. Sebagai praktik terbaik, selalu pilih aturan penghapusan SQL berdasarkan logika bisnis dan semantik data, alih-alih mengutamakan kemudahan atau kebutuhan jangka pendek.

Saya merekomendasikan mengambil kursus Database Design, di mana Anda akan belajar membuat dan mengelola basis data serta memilih DBMS yang sesuai dengan kebutuhan Anda. Saya juga merekomendasikan mencoba jalur karier Associate Data Engineer in SQL untuk mempelajari dasar-dasar rekayasa data dan data warehousing.