Ringkasan. dalam tutorial ini, Anda akan mempelajari cara menggunakan batasan kunci asing MariaDB untuk menegakkan integritas referensial antar tabel
Apa itu kunci asing
Kunci asing adalah kolom atau kumpulan kolom dalam tabel yang mereferensikan kolom atau kumpulan kolom di tabel lain, yang memberlakukan integritas referensial antara dua tabel
Tabel yang memiliki kunci asing disebut tabel anak sedangkan tabel yang menjadi referensi kunci asing disebut tabel induk
Biasanya, kolom kunci asing di tabel anak merujuk ke kolom kunci utama dari tabel induk
Lihat tabel
alter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)_3 danalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)4 berikut dari database sampelalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)5alter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)6 di tabelalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)4 adalah kunci asing yang merujuk ke kolomalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)6 di tabelalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)3Untuk setiap baris di tabel
alter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)_4, Anda dapat menemukan baris yang sesuai di tabelalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)3Batasan kunci asing mencegah Anda memasukkan baris ke dalam tabel
alter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)4 tanpa baris yang sesuai di tabelalter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)3. Dengan kata lain, suatu negara tidak akan ada tanpa adanya suatu wilayahSebuah tabel dapat memiliki beberapa kunci asing yang mengacu pada kunci utama dari tabel induk yang berbeda
Mendefinisikan batasan kunci asing
Untuk menentukan kunci asing untuk tabel, Anda menggunakan batasan kunci asing. Biasanya, Anda menentukan batasan kunci asing untuk tabel pada saat pembuatan tabel
Berikut ini mengilustrasikan sintaks mendefinisikan batasan kunci asing
create table table_name( column_list, ...,
Code language: SQL (Structured Query Language) (sql)[batasan kendala_nama]
referensi kunci asing [fk_name](column_list) parent_table(column_list)
[saat menghapus reference_option]
[pada pembaruan reference_option]
);
Dalam sintaks ini
- Pertama, tentukan nama batasan kunci asing setelah kata kunci
create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)4. MariaDB secara implisit akan memberikan nama yang dihasilkan jika Anda melewatkan klausacreate table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)4 - Kedua, tentukan nama kunci asing diikuti dengan daftar nama kolom yang dipisahkan koma yang ditempatkan di dalam tanda kurung. Nama kunci asing bersifat opsional
- Ketiga, tentukan tabel induk dengan daftar kolom yang dirujuk oleh kolom kunci asing setelah kata kunci
create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)6 - Terakhir, tentukan bagaimana batasan kunci asing mempertahankan integritas referensial antara dua tabel menggunakan klausa
create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)7 dancreate table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)8
create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)_9 memberikan tindakan yang akan dilakukan MariaDB saat nilai dalam tabel induk dihapus (create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)7) dan/atau diperbarui (create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)_9 menerima salah satu dari lima nilai berikut dengan efek yang sesuaiinsert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)3. ketika sebuah baris dalam tabel induk dihapus atau diperbarui, baris terkait dalam tabel anak secara otomatis dihapus atau diperbaruiinsert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)4. ketika baris dalam tabel induk dihapus atau diperbarui, baris terkait dalam tabel anak diatur ke nolinsert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)5. jika baris dalam tabel induk memiliki baris referensi dalam tabel anak, baris tersebut tidak dapat diperbarui atau dihapusinsert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)6. memiliki efek yang sama dengan membatasiinsert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)7. hanya bekerja dengan mesin penyimpanan PBXT usang. Kolom kunci asing akan disetel ke nilai defaultnya jika kolom di tabel induk diperbarui atau dihapus. Jika tidak ada nilai default yang tersedia, MariaDB akan memunculkan error
Secara default, MariaDB menggunakan opsi
insert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)_5 jika Anda tidak menentukan klausacreate table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)7 ataucreate table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)8Menambahkan batasan kunci asing ke tabel
Untuk menambahkan batasan kunci asing ke tabel yang sudah ada, Anda menggunakan pernyataan
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)1alter table table_name
Code language: SQL (Structured Query Language) (sql)[batasan fk_constraint_name]
referensi kunci asing [fk_name](column_list) parent_table(column_list)
[saat menghapus reference_option]
[pada pembaruan reference_option]
Menghapus batasan asing dari tabel
Untuk menghapus batasan asing, Anda menggunakan pernyataan
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)2alter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)Contoh kendala kunci asing MariaDB
Mari kita ambil beberapa contoh penggunaan batasan kunci asing dengan berbagai opsi referensi
Menyiapkan tabel sampel
Pertama, buat tabel baru bernama
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)3create table gadget_types( type_id int auto_increment, name varchar(100) not null, primary key(type_id) );
Code language: SQL (Structured Query Language) (sql)Kedua, masukkan beberapa baris ke dalam tabel ________29______3
insert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)Ketiga, buat tabel lain bernama
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)Tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)_5 memiliki kunci asing (create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7) yang mereferensikan kolomcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)3constraint fk_type foreign key(type_id) references gadget_types(type_id)
Code language: SQL (Structured Query Language) (sql)Terakhir, masukkan beberapa baris ke dalam tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
insert into gadgets(gadget_name,type_id) values ('Amazon Kindle',1), ('Apple iPod',1), ('Audio Highway Listen Up',1), ('Apple iPad',2), ('MicroSD',2), ('Apple iPhone',3), ('BlackBerry 6210',3), ('Pager',3), ('Air Taser Model 34000',4), ('Credit Card',4), ('Zippo',4), ('Casio G-Shock DW-5000C',4), ('Nikon F',5), ('Canon EOS 5D Mark II',5);
Code language: SQL (Structured Query Language) (sql)Opsi referensi
insert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)_5Pernyataan berikut mencoba untuk menghapus baris dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)3delete from gadget_types where type_id = 1;
Code language: SQL (Structured Query Language) (sql)MariaDB mengeluarkan kesalahan berikut
SQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` FOREIGN KEY (`type_id`) REFERENCES `gadget_types` (`type_id`))
Code language: SQL (Structured Query Language) (sql)Kesalahan terjadi karena opsi referensi
insert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)5Untuk menghapus baris dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)_3, Anda harus menghapus semua baris referensi dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 terlebih dahuluOpsi referensi
insert into gadget_types(name) values ('Entertainment'), ('Computing'), ('Communication'), ('Lifestyle'), ('Cameras');
Code language: SQL (Structured Query Language) (sql)_4Pertama, jatuhkan batasan kunci asing
constraint fk_type foreign key(type_id) references gadget_types(type_id)
Code language: SQL (Structured Query Language) (sql)7 dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5alter table table_name
Code language: SQL (Structured Query Language) (sql)0Kedua, tambahkan batasan kunci asing ke tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 dengan opsiinsert into gadgets(gadget_name,type_id) values ('Amazon Kindle',1), ('Apple iPod',1), ('Audio Highway Listen Up',1), ('Apple iPad',2), ('MicroSD',2), ('Apple iPhone',3), ('BlackBerry 6210',3), ('Pager',3), ('Air Taser Model 34000',4), ('Credit Card',4), ('Zippo',4), ('Casio G-Shock DW-5000C',4), ('Nikon F',5), ('Canon EOS 5D Mark II',5);
Code language: SQL (Structured Query Language) (sql)0 daninsert into gadgets(gadget_name,type_id) values ('Amazon Kindle',1), ('Apple iPod',1), ('Audio Highway Listen Up',1), ('Apple iPad',2), ('MicroSD',2), ('Apple iPhone',3), ('BlackBerry 6210',3), ('Pager',3), ('Air Taser Model 34000',4), ('Credit Card',4), ('Zippo',4), ('Casio G-Shock DW-5000C',4), ('Nikon F',5), ('Canon EOS 5D Mark II',5);
Code language: SQL (Structured Query Language) (sql)1alter table table_name
Code language: SQL (Structured Query Language) (sql)1Ketiga, hapus gadget type id 1 dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)3alter table table_name
Code language: SQL (Structured Query Language) (sql)2Keempat, kueri data dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5alter table table_name
Code language: SQL (Structured Query Language) (sql)3Seperti yang ditunjukkan dengan jelas dari keluaran, nilai dalam
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 kolom baris dengancreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 1 dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 disetel ke nol karena opsiinsert into gadgets(gadget_name,type_id) values ('Amazon Kindle',1), ('Apple iPod',1), ('Audio Highway Listen Up',1), ('Apple iPad',2), ('MicroSD',2), ('Apple iPhone',3), ('BlackBerry 6210',3), ('Pager',3), ('Air Taser Model 34000',4), ('Credit Card',4), ('Zippo',4), ('Casio G-Shock DW-5000C',4), ('Nikon F',5), ('Canon EOS 5D Mark II',5);
Code language: SQL (Structured Query Language) (sql)0.Kelima, perbarui jenis gadget dari 2 menjadi 20 di tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)3alter table table_name
Keenam, kueri data dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5alter table table_name
Code language: SQL (Structured Query Language) (sql)3Nilai-nilai dalam kolom
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 baris dengancreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 2 dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 ditetapkan ke nol karena opsiinsert into gadgets(gadget_name,type_id) values ('Amazon Kindle',1), ('Apple iPod',1), ('Audio Highway Listen Up',1), ('Apple iPad',2), ('MicroSD',2), ('Apple iPhone',3), ('BlackBerry 6210',3), ('Pager',3), ('Air Taser Model 34000',4), ('Credit Card',4), ('Zippo',4), ('Casio G-Shock DW-5000C',4), ('Nikon F',5), ('Canon EOS 5D Mark II',5);
Code language: SQL (Structured Query Language) (sql)1.Terakhir, hapus baris anak yatim dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5alter table table_name
Code language: SQL (Structured Query Language) (sql)6Opsi referensi
delete from gadget_types where type_id = 1;
Code language: SQL (Structured Query Language) (sql)_5Pertama, jatuhkan batasan kunci asing
constraint fk_type foreign key(type_id) references gadget_types(type_id)
Code language: SQL (Structured Query Language) (sql)7 dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5alter table table_name
Code language: SQL (Structured Query Language) (sql)0Kedua, tambahkan batasan kunci asing ke tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 dengan opsidelete from gadget_types where type_id = 1;
Code language: SQL (Structured Query Language) (sql)9 danSQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` FOREIGN KEY (`type_id`) REFERENCES `gadget_types` (`type_id`))
Code language: SQL (Structured Query Language) (sql)0alter table table_name
Code language: SQL (Structured Query Language) (sql)8Ketiga, hapus
SQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` FOREIGN KEY (`type_id`) REFERENCES `gadget_types` (`type_id`))
Code language: SQL (Structured Query Language) (sql)1 id 3 dari tabelcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)3alter table table_name
Code language: SQL (Structured Query Language) (sql)_9MariaDB secara otomatis menghapus baris dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 yangcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 adalah 3 karena tindakanSQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` FOREIGN KEY (`type_id`) REFERENCES `gadget_types` (`type_id`))
Code language: SQL (Structured Query Language) (sql)5alter table table_name
Code language: SQL (Structured Query Language) (sql)3Keempat, update
SQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` FOREIGN KEY (`type_id`) REFERENCES `gadget_types` (`type_id`))
Code language: SQL (Structured Query Language) (sql)1 id 4 ke 40.alter table table_name drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)_1MariaDB secara otomatis memperbarui baris dari tabel
create table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)5 yangcreate table gadgets( gadget_id int auto_increment, gadget_name varchar(100) not null, type_id int, primary key(gadget_id), constraint fk_type foreign key(type_id) references gadget_types(type_id) );
Code language: SQL (Structured Query Language) (sql)7 adalah 4 hingga 40 karena tindakanSQL Error (1451): Cannot delete or update a parent row: a foreign key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` FOREIGN KEY (`type_id`) REFERENCES `gadget_types` (`type_id`))
Code language: SQL (Structured Query Language) (sql)0alter table table_name
Code language: SQL (Structured Query Language) (sql)3Dalam tutorial ini, Anda telah belajar tentang batasan kunci asing MariaDB dan cara menggunakannya untuk menerapkan integritas referensial antar tabel.