Cara menambahkan kunci asing di meja kerja mysql

Question

Ringkasan. dalam tutorial ini, Anda akan belajar tentang kunci asing MySQL dan cara membuat, menjatuhkan, dan menonaktifkan batasan kunci asing

Table of Contents Show

Pengantar kunci asing MySQL
Kunci asing referensi sendiri
Sintaks MySQL ________26______1
MySQL INSERT INTO products(productName, categoryId) VALUES('iPad',3);Code language: SQL (Structured Query Language) (sql)_1 contoh
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)4 & Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)3 tindakan
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)_1 tindakan
SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)8 tindakan
Jatuhkan batasan kunci asing MySQL
Menonaktifkan pemeriksaan kunci asing
Bagaimana cara menambahkan kunci asing ke tabel yang ada di MySQL?
Bagaimana cara melihat kunci asing di MySQL Workbench?
Bagaimana cara mengaktifkan batasan kunci asing di MySQL?
Bagaimana cara menambahkan kendala di MySQL Workbench?

Pengantar kunci asing MySQL

Kunci asing adalah kolom atau grup kolom dalam tabel yang terhubung ke kolom atau grup kolom di tabel lain. Kunci asing menempatkan batasan pada data dalam tabel terkait, yang memungkinkan MySQL mempertahankan integritas referensial

Mari kita lihat tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

5 dan

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

6 berikut dari database sampel

Dalam diagram ini, setiap pelanggan dapat memiliki nol atau banyak pesanan dan setiap pesanan dimiliki oleh satu pelanggan

Hubungan antara tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

5 dan tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

6 adalah satu-ke-banyak. Dan hubungan ini dibuat oleh kunci asing di tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

6 yang ditentukan oleh kolom

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

0

Kolom

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

_0 di tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

6 terhubung ke kolom

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

0 primary key di tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

5

Tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

5 disebut tabel induk atau tabel referensi, dan tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

6 disebut tabel anak atau tabel referensi

Biasanya, kolom kunci asing dari tabel anak sering merujuk ke kolom kunci utama dari tabel induk

Sebuah tabel dapat memiliki lebih dari satu kunci asing di mana setiap kunci asing merujuk ke kunci utama dari tabel induk yang berbeda

Setelah batasan kunci asing diberlakukan, kolom kunci asing dari tabel anak harus memiliki baris yang sesuai di kolom kunci induk dari tabel induk atau nilai dalam kolom kunci asing ini harus

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

7 (lihat contoh tindakan

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

8 di bawah)

Misalnya, setiap baris dalam tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

_6 memiliki

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

0 yang ada di kolom

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

0 dari tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

5. Beberapa baris dalam tabel

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

_6 dapat memiliki

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

0 yang sama

Kunci asing referensi sendiri

Terkadang, tabel anak dan orang tua dapat merujuk ke tabel yang sama. Dalam hal ini, kunci asing merujuk kembali ke kunci utama dalam tabel yang sama

Lihat tabel

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

_5 berikut dari database sampel

Kolom

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

_6 adalah kunci asing yang mengacu pada kolom

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

7 yang merupakan kunci utama dari tabel

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

5

Hubungan ini memungkinkan tabel

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

_5 untuk menyimpan struktur pelaporan antara karyawan dan manajer. Setiap karyawan melapor kepada nol atau satu karyawan dan seorang karyawan dapat memiliki nol atau banyak bawahan

Kunci asing pada kolom

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

_6 dikenal sebagai kunci asing rekursif atau self-referensi

Sintaks MySQL __261

Berikut adalah sintaks dasar untuk mendefinisikan batasan kunci asing dalam pernyataan

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

2 atau

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

3

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (column_name, ...)
REFERENCES parent_table(colunm_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
Code language: SQL (Structured Query Language) (sql)

Dalam sintaks ini

Pertama, tentukan nama batasan kunci asing yang ingin Anda buat setelah kata kunci

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

4. Jika Anda menghilangkan nama kendala, MySQL secara otomatis menghasilkan nama untuk kendala kunci asing

Kedua, tentukan daftar kolom kunci asing yang dipisahkan koma setelah kata kunci

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

1. Nama kunci asing juga opsional dan dihasilkan secara otomatis jika Anda melewatkannya

Ketiga, tentukan tabel induk diikuti dengan daftar kolom yang dipisahkan koma yang dirujuk oleh kolom kunci asing

Terakhir, tentukan bagaimana kunci asing mempertahankan integritas referensial antara tabel anak dan induk dengan menggunakan klausa

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

6 dan

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

7.

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

_8 menentukan tindakan yang akan diambil MySQL ketika nilai dalam kolom kunci induk dihapus (

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

6) atau diperbarui (

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

7)

MySQL memiliki lima opsi referensi.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

1,

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

8,

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

3,

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4, dan

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_1. jika baris dari tabel induk dihapus atau diperbarui, nilai baris yang cocok di tabel anak secara otomatis dihapus atau diperbarui

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

_8. jika baris dari tabel induk dihapus atau diperbarui, nilai kolom (atau kolom) kunci asing di tabel anak disetel ke

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

7

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_4. jika baris dari tabel induk memiliki baris yang cocok di tabel anak, MySQL menolak menghapus atau memperbarui baris di tabel induk

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_3. sama dengan

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_4

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_5. dikenali oleh parser MySQL. Namun, tindakan ini ditolak oleh tabel InnoDB dan NDB

Faktanya, MySQL sepenuhnya mendukung tiga tindakan.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4,

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

1 dan

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

8

Jika Anda tidak menentukan klausa

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

6 dan

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

7, tindakan defaultnya adalah

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

MySQL INSERT INTO products(productName, categoryId) VALUES('iPad',3);Code language: SQL (Structured Query Language) (sql)_1 contoh

Mari buat database baru bernama

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

0 untuk demonstrasi

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)4 & Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)3 tindakan

Di dalam database

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_0, buat dua tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4 dan

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_6 di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 adalah kolom kunci asing yang merujuk ke kolom

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 di

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4 tabel

Karena kami tidak menentukan klausa

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

7 dan

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

6, tindakan default adalah

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4 untuk operasi pembaruan dan penghapusan

Langkah-langkah berikut mengilustrasikan tindakan

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_4

1) Masukkan dua baris ke dalam tabel ________56______4

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

2) Pilih data dari tabel ________56______4

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

3) Masukkan baris baru ke dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5.

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Code language: SQL (Structured Query Language) (sql)

Ini berfungsi karena

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_6 1 ada di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

4) Mencoba menyisipkan baris baru ke dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 dengan nilai

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 tidak ada dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);
Code language: SQL (Structured Query Language) (sql)

MySQL mengeluarkan kesalahan berikut

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5) Perbarui nilai di kolom

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_6 di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4 ke

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

14

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;
Code language: SQL (Structured Query Language) (sql)

MySQL mengeluarkan kesalahan ini

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

Karena opsi

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4, Anda tidak dapat menghapus atau memperbarui

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_16 karena direferensikan oleh

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

17

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

18 di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)_1 tindakan

Langkah-langkah ini menggambarkan cara kerja tindakan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

21 dan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

22

1) Jatuhkan tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_0

2) Buat tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_5 dengan opsi

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

21 dan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

26 untuk kunci asing

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_1

3) Masukkan empat baris ke dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_2

4) Pilih data dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

3

5) Perbarui

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 1 hingga 100 di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4.

CREATE DATABASE fkdemo;

USE fkdemo;



Code language: SQL (Structured Query Language) (sql)

_4

6) Verifikasi pembaruan

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

7) Dapatkan data dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5.

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

3

Seperti yang Anda lihat, dua baris dengan nilai

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

18 di kolom

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 secara otomatis diperbarui ke

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

14 karena tindakan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

21.

8) Hapus

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_6 2 dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_7

9) Verifikasi penghapusan

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

10) Periksa tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5.

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

3

Semua produk dengan

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 2 dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 otomatis dihapus karena tindakan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

26.

SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)8 tindakan

Langkah-langkah ini menggambarkan cara kerja tindakan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_44 dan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

45

1) Jatuhkan tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_4 dan

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

0

2) Buat tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_4 dan

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

1

Kunci asing di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_5 diubah menjadi opsi

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

44 dan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

45

3) Masukkan baris ke dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

2

4) Masukkan baris ke dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_2

5) Perbarui

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_6 dari 1 hingga 100 di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_4

6) Verifikasi pembaruan

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

7) Pilih data dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5.

Baris dengan

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 1 di tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 secara otomatis disetel ke

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

7 karena tindakan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

44

8) Hapus

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_6 2 dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

4

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

6

9) Periksa tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

_5

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

3

Nilai-nilai dalam kolom

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 dari baris dengan

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

6 2 dalam tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 secara otomatis diatur ke

SELECT * FROM categories;
Code language: SQL (Structured Query Language) (sql)

7 karena tindakan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

45.

Jatuhkan batasan kunci asing MySQL

Untuk menghapus batasan kunci asing, Anda menggunakan pernyataan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

70

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

8

Dalam sintaks ini

Pertama, tentukan nama tabel tempat Anda ingin melepaskan kunci asing setelah kata kunci

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

70

Kedua, tentukan nama batasan setelah kata kunci

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

72

Perhatikan bahwa

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

73 adalah nama batasan kunci asing yang ditentukan saat Anda membuat atau menambahkan batasan kunci asing ke tabel

Untuk mendapatkan nama kendala yang dihasilkan dari tabel, Anda menggunakan pernyataan

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

74

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

_9

Misalnya, untuk melihat kunci asing dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5, Anda menggunakan pernyataan berikut

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

0

Berikut adalah output dari pernyataan tersebut

Seperti yang Anda lihat dengan jelas dari output, tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5 tabel memiliki satu batasan kunci asing.

CREATE DATABASE fkdemo;

USE fkdemo;
Code language: SQL (Structured Query Language) (sql)

_77

Dan pernyataan ini menghilangkan batasan kunci asing dari tabel

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)
Code language: JavaScript (javascript)

5

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

_1

Untuk memastikan batasan kunci asing telah dihilangkan, Anda dapat melihat struktur tabel produk

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

0

Menonaktifkan pemeriksaan kunci asing

Kadang-kadang, sangat berguna untuk menonaktifkan pemeriksaan kunci asing e. g. , saat Anda mengimpor data dari file CSV ke dalam tabel. Jika Anda tidak menonaktifkan pemeriksaan kunci asing, Anda harus memuat data dengan urutan yang tepat i. e. , Anda harus memuat data ke tabel induk terlebih dahulu lalu tabel anak, yang bisa membosankan. Namun, jika Anda menonaktifkan pemeriksaan kunci asing, Anda dapat memuat data ke dalam tabel dalam urutan apa pun

Untuk menonaktifkan pemeriksaan kunci asing, Anda menggunakan pernyataan berikut

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

_3

Dan Anda dapat mengaktifkannya dengan menggunakan pernyataan berikut

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

_4

Dalam tutorial ini, Anda telah mempelajari tentang kunci asing MySQL dan cara membuat batasan kunci asing dengan berbagai opsi referensi

Bagaimana cara menambahkan kunci asing ke tabel yang ada di MySQL?

Berikut sintaks pernyataan ALTER TABLE untuk menambahkan foreign key pada tabel yang sudah ada. .

ALTER TABLE table_name

TAMBAHKAN [KENDALA [simbol]] KUNCI ASING

[nama_indeks] (nama_kolom,. )

REFERENSI nama_tabel (nama_kolom,. )

PADA DELETE referenceOption

ON UPDATE referenceOption

Bagaimana cara melihat kunci asing di MySQL Workbench?

Di Editor Tabel buka tab Kunci Asing (di bagian bawah) . Kunci ditampilkan di panel kiri dan detail kunci yang dipilih ditampilkan di sebelah kanan. Anda dapat melihat pasangan kolom asing dan utama di kolom Kolom dan Kolom Referensi di kisi di tengah jendela.

Bagaimana cara mengaktifkan batasan kunci asing di MySQL?

Di MySQL, tabel InnoDB dan NDB mendukung pemeriksaan batasan kunci asing. Pemeriksaan kunci asing dikontrol oleh variabel foreign_key_checks, yang diaktifkan secara default . Biasanya, Anda membiarkan variabel ini diaktifkan selama operasi normal untuk menerapkan integritas referensial.

Bagaimana cara menambahkan kendala di MySQL Workbench?

Dalam tabel MySQL, setiap kolom harus berisi nilai (termasuk NULL). Saat memasukkan data ke dalam tabel, jika tidak ada nilai yang diberikan ke kolom, maka kolom tersebut mendapatkan nilai yang ditetapkan sebagai DEFAULT. Sintaksis. BUAT TABEL [nama tabel] ([nama kolom] [tipe data]([ukuran]) [batasan kolom] ….

kode mysql