DDL Lanjutan: Membangun dan Mengelola Relasi Antar Tabel di MySQL

Pada saat mengembangkan aplikasi berbasis database, sering kali diperlukan untuk mengelola relasi antar tabel. Dalam sistem manajemen basis data MySQL, relasi antar tabel dapat dibangun dan dikelola dengan menggunakan perintah Data Definition Language (DDL). Dalam artikel ini, kita akan fokus pada langkah-langkah untuk membuat relasi antar tabel, menghapus relasi, mengubah nama field yang berelasi, dan mengubah tipe kolom yang berelasi menggunakan database "country" dan tabel "provinces" dan "regencies" di MySQL.

Membuat Relasi Antar Tabel

Langkah pertama dalam membangun relasi antar tabel adalah membuat tabel-tabel yang terlibat dalam relasi tersebut. Dalam contoh ini, kita memiliki tabel "provinces" dan "regencies" dalam database "country". Tabel "provinces" memiliki kolom "code" yang akan berelasi dengan kolom "province_code" di tabel "regencies". Relasi yang dibangun adalah 1 provinces memiliki banyak regencies.

Berikut adalah contoh sintaks untuk membuat tabel-tabel ini:

CREATE TABLE regencies (
    code INT PRIMARY KEY,
    name VARCHAR(50),
    province_code INT,
    FOREIGN KEY (province_code) REFERENCES provinces(code)
);

CREATE TABLE provinces (
    code INT PRIMARY KEY,
    name VARCHAR(50)
);

Dalam contoh di atas, kita menggunakan perintah CREATE TABLE untuk membuat tabel "regencies" dengan kolom "code", "name", dan "province_code", dan tabel "provinces" dengan kolom "code" dan "name". Kemudian, kita menggunakan perintah FOREIGN KEY untuk mendefinisikan relasi antara kolom "province_code" di tabel "regencies" dengan kolom "code" di tabel "provinces".

Menghapus Relasi Antar Tabel

Jika pada suatu waktu kita perlu menghapus relasi antar tabel, kita dapat menggunakan perintah ALTER TABLE untuk mengubah struktur tabel. Berikut adalah contoh sintaks untuk menghapus relasi antar tabel dalam contoh ini:

ALTER TABLE regencies
DROP FOREIGN KEY FK_regencies_provinces;

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE dengan opsi DROP FOREIGN KEY untuk menghapus relasi antar tabel. "FK_regencies_provinces" adalah nama relasi yang akan dihapus.

Mengubah Nama Field yang Berelasi

Jika kita perlu mengubah nama field yang berelasi, kita dapat menggunakan perintah ALTER TABLE untuk mengubah nama kolom dalam tabel. Berikut adalah contoh sintaks untuk mengubah nama field "code" menjadi "province_code" dalam tabel "regencies":

ALTER TABLE regencies
CHANGE COLUMN code province_code INT;

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE dengan opsi CHANGE COLUMN untuk mengubah nama kolom "code" menjadi "province_code" dalam tabel "regencies". Kami juga mengubah tipe kolom menjadi INT.

Mengubah Tipe Kolom yang Berelasi

Jika kita perlu mengubah tipe kolom yang berelasi, kita juga dapat menggunakan perintah ALTER TABLE. Berikut adalah contoh sintaks untuk mengubah tipe kolom "code" menjadi VARCHAR(10) dalam tabel "provinces"

ALTER TABLE provinces
MODIFY COLUMN code VARCHAR(10);

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE dengan opsi MODIFY COLUMN untuk mengubah tipe kolom "code" menjadi VARCHAR(10) dalam tabel "provinces".

Opsi Relasi Antar Table

Di sini saya akan menjelaskan materi tentang constraint ON UPDATE / ON DELETE yang mencakup opsi "NO ACTION", "RESTRICT", "CASCADE", "SET NULL", dan "SET DEFAULT" yang digunakan dalam membangun relasi antar tabel di MySQL.

NO ACTION:

Ketika menggunakan opsi NO ACTION, MySQL tidak melakukan tindakan apa pun saat terjadi pembaruan atau penghapusan pada tabel yang memiliki relasi. Opsi ini berfungsi sebagai opsi default jika tidak ditentukan secara eksplisit. Jika ada upaya untuk memperbarui atau menghapus baris yang memiliki ketergantungan, MySQL akan menghasilkan kesalahan dan operasi tidak akan dilakukan.

CREATE TABLE regencies (
    code INT PRIMARY KEY,
    name VARCHAR(50),
    province_code INT,
    FOREIGN KEY (province_code) REFERENCES provinces(code)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

RESTRICT:

Opsi RESTRICT bekerja serupa dengan NO ACTION. Jika ada upaya untuk memperbarui atau menghapus baris yang memiliki ketergantungan, MySQL akan menghasilkan kesalahan dan operasi tidak akan dilakukan. RESTRICT juga merupakan opsi default jika tidak ditentukan secara eksplisit.

CREATE TABLE regencies (
    code INT PRIMARY KEY,
    name VARCHAR(50),
    province_code INT,
    FOREIGN KEY (province_code) REFERENCES provinces(code)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
);

CASCADE:

Opsi CASCADE memungkinkan perubahan atau penghapusan baris yang memiliki ketergantungan, dan secara otomatis memperbarui atau menghapus baris yang terkait di tabel lain. Misalnya, jika kita memperbarui atau menghapus baris di tabel "provinces", maka semua baris yang memiliki ketergantungan di tabel "regencies" akan diperbarui atau dihapus secara otomatis.

CREATE TABLE regencies (
    code INT PRIMARY KEY,
    name VARCHAR(50),
    province_code INT,
    FOREIGN KEY (province_code) REFERENCES provinces(code)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

SET NULL:

Opsi SET NULL memungkinkan perubahan atau penghapusan baris yang memiliki ketergantungan, dan secara otomatis mengatur nilai kolom yang berelasi ke NULL di tabel terkait. Jadi, jika kita memperbarui atau menghapus baris di tabel "provinces", maka nilai kolom "province_code" di tabel "regencies" akan diatur menjadi NULL.

CREATE TABLE regencies (
    code INT PRIMARY KEY,
    name VARCHAR(50),
    province_code INT,
    FOREIGN KEY (province_code) REFERENCES provinces(code)
        ON UPDATE SET NULL
        ON DELETE SET NULL
);

SET DEFAULT:

Opsi SET DEFAULT memungkinkan perubahan atau penghapusan baris yang memiliki ketergantungan, dan secara otomatis mengatur nilai kolom yang berelasi ke nilai default yang ditentukan di tabel terkait. Jadi, jika kita memperbarui atau menghapus baris di tabel "provinces", maka nilai kolom "province_code" di tabel "regencies" akan diatur ke nilai default yang telah ditentukan.

CREATE TABLE regencies (
    code INT PRIMARY KEY,
    name VARCHAR(50),
    province_code INT,
    FOREIGN KEY (province_code) REFERENCES provinces(code)
        ON UPDATE SET DEFAULT
        ON DELETE SET DEFAULT
);

Penggunaan opsi constraint ON UPDATE / ON DELETE sangat penting dalam membangun relasi antar tabel yang aman dan konsisten dalam database. Dalam perancangan basis data, pilihan yang tepat harus dipilih berdasarkan kebutuhan aplikasi dan hubungan antar tabel yang diinginkan.

Perubahan Opsi Relasi Dengan Alter Table

Mengubah NO ACTION menjadi CASCADE:

ALTER TABLE regencies
DROP FOREIGN KEY FK_regencies_provinces;

ALTER TABLE regencies
ADD CONSTRAINT FK_regencies_provinces
FOREIGN KEY (province_code) REFERENCES provinces(code)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE untuk menghapus constraint FOREIGN KEY yang ada dan kemudian menambahkannya kembali dengan opsi ON UPDATE dan ON DELETE diubah menjadi CASCADE.

Mengubah CASCADE menjadi SET NULL:

ALTER TABLE regencies
DROP FOREIGN KEY FK_regencies_provinces;

ALTER TABLE regencies
ADD CONSTRAINT FK_regencies_provinces
FOREIGN KEY (province_code) REFERENCES provinces(code)
    ON UPDATE SET NULL
    ON DELETE SET NULL;

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE untuk menghapus constraint FOREIGN KEY yang ada dan kemudian menambahkannya kembali dengan opsi ON UPDATE dan ON DELETE diubah menjadi SET NULL.

Mengubah SET NULL menjadi RESTRICT:

ALTER TABLE regencies
DROP FOREIGN KEY FK_regencies_provinces;

ALTER TABLE regencies
ADD CONSTRAINT FK_regencies_provinces
FOREIGN KEY (province_code) REFERENCES provinces(code)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT;

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE untuk menghapus constraint FOREIGN KEY yang ada dan kemudian menambahkannya kembali dengan opsi ON UPDATE dan ON DELETE diubah menjadi RESTRICT.

Mengubah SET DEFAULT menjadi NO ACTION:

ALTER TABLE regencies
DROP FOREIGN KEY FK_regencies_provinces;

ALTER TABLE regencies
ADD CONSTRAINT FK_regencies_provinces
FOREIGN KEY (province_code) REFERENCES provinces(code)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

Dalam contoh di atas, kita menggunakan perintah ALTER TABLE untuk menghapus constraint FOREIGN KEY yang ada dan kemudian menambahkannya kembali dengan opsi ON UPDATE dan ON DELETE diubah menjadi NO ACTION.

Mendapatkan Index dari table regencies

Untuk mendapatkan informasi mengenai indeks kolom dari tabel "regencies" di MySQL, kita dapat menggunakan perintah SHOW INDEX atau mengkonsultasikan metadata tabel sistem information_schema.

Menggunakan Perintah SHOW INDEX:

SHOW INDEX FROM regencies;

Perintah di atas akan menampilkan informasi indeks kolom dari tabel "regencies". Hasilnya akan mencakup kolom-kolom seperti "Table", "Non_unique", "Key_name", "Seq_in_index", "Column_name", "Collation", "Cardinality", dll. Informasi ini memberikan gambaran tentang indeks yang ada pada tabel tersebut.

Menggunakan Metadata Tabel information_schema:

SELECT *
FROM information_schema.statistics
WHERE table_schema = 'country'
    AND table_name = 'regencies';

Summary

Dengan menggunakan langkah-langkah di atas, kita dapat membangun relasi antara tabel "provinces" dan "regencies" dalam database "country". Relasi yang terbentuk adalah setiap province memiliki banyak regencies, di mana kolom "province_code" di tabel "regencies" mengacu pada kolom "code" di tabel "provinces". Jika diperlukan, kita juga dapat menghapus relasi, mengubah nama field yang berelasi, atau mengubah tipe kolom yang berelasi menggunakan perintah-perintah DDL yang disediakan oleh MySQL.

Penting untuk memperhatikan bahwa sintaks yang disediakan di atas hanya merupakan contoh, dan perlu disesuaikan dengan kebutuhan spesifik dalam pengembangan aplikasi atau sistem basis data yang sedang dikerjakan.