Beberapa kolom kunci asing mysql

Question

Kunci asing adalah kolom (atau kombinasi kolom) dalam tabel yang nilainya harus cocok dengan nilai kolom di beberapa tabel lain.

> UPDATE customers SET id = 1002 WHERE id = 1001;

9 kendala menegakkan integritas referensial, yang pada dasarnya mengatakan bahwa jika nilai kolom A mengacu pada nilai kolom B, maka nilai kolom B harus ada

Table of Contents Show

Aturan untuk membuat kunci asing
Pencocokan kunci asing komposit
Tindakan kunci asing
Pertunjukan
Tingkat kolom
Tingkat meja
Contoh penggunaan
Gunakan batasan kunci asing dengan tindakan default
Gunakan Batasan Kunci Asing dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 56
Gunakan Batasan Kunci Asing dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 54
Gunakan Batasan Kunci Asing dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 55
Tambahkan beberapa batasan kunci asing ke satu kolom
Cocokkan kunci asing gabungan dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); _22 dan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 21
Bisakah kunci asing menjadi beberapa kolom?
Bisakah kunci asing mereferensikan banyak kolom dari tabel induk?
Bisakah kunci asing menjadi bagian dari banyak tabel?
Berapa banyak kolom yang dapat Anda miliki sebagai kunci asing dalam tabel?

Misalnya, diberikan tabel

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

0 dan tabel

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

1, jika Anda membuat kolom

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

2 yang mereferensikan

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

3 primary key

Setiap nilai yang dimasukkan atau diperbarui di

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

_2 harus sama persis dengan nilai di

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

3, atau menjadi

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6

Nilai dalam

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

_3 yang direferensikan oleh

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

2 tidak dapat dihapus atau diperbarui, kecuali jika Anda memiliki. Namun, nilai

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

_3 yang tidak ada di

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

2 dapat dihapus atau diperbarui

Untuk mempelajari lebih lanjut tentang dasar-dasar kunci asing, tonton video berikut

Tip

Untuk membaca selengkapnya tentang cara kerja kunci asing, lihat Apa itu Kunci Asing?

Detail

Aturan untuk membuat kunci asing

Kolom Kunci Asing

Kolom kunci asing harus menggunakan jenis kolom yang direferensikan
Kolom kunci asing tidak dapat berupa kolom virtual yang dihitung, tetapi dapat berupa kolom terhitung yang disimpan
Satu kolom dapat memiliki beberapa batasan kunci asing. Sebagai contoh, lihat

Kolom kunci asing dapat mereferensikan dalam tabel meskipun kolom

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

01 tidak secara eksplisit merupakan bagian dari batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

04. Ini dimungkinkan karena

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_01 secara implisit disertakan dalam setiap indeks pada tabel

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

02 sebagai kunci partisi. Ini berlaku untuk kolom mana pun yang digunakan sebagai kolom partisi, jika nama yang berbeda digunakan melalui

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

07

Kolom Referensi

Kolom yang direferensikan hanya boleh berisi rangkaian nilai yang unik. Ini berarti klausa

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

08 harus menggunakan kolom yang persis sama dengan batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

04 atau

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

10 pada tabel yang direferensikan. Misalnya, klausa

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_11 mensyaratkan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

12 untuk memiliki kendala

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

13 atau

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

14. Urutan kolom dalam definisi kunci asing tidak perlu cocok dengan urutan kolom dalam batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

04 atau

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

10 yang sesuai

Dalam klausa

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_08, jika Anda menentukan tabel tetapi tidak ada kolom, CockroachDB mereferensikan kunci primer tabel. Dalam kasus ini, batasan

> UPDATE customers SET id = 1002 WHERE id = 1001;

_9 dan kunci utama tabel yang direferensikan harus berisi jumlah kolom yang sama

Secara default, kolom referensi harus berada dalam database yang sama dengan kolom referensi kunci asing. Untuk mengaktifkan referensi kunci asing lintas-database, atur pengaturan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

19 ke

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

20

Nilai nol

Kunci asing satu kolom menerima nilai nol

Kunci asing multi-kolom (komposit) hanya menerima nilai nol dalam skenario berikut

Penulisan berisi nilai null untuk semua kolom kunci asing (jika

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21 ditentukan)

Penulisan berisi nilai nol untuk setidaknya satu kolom kunci asing (jika

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22 ditentukan)

Untuk informasi selengkapnya tentang kunci asing komposit, lihat bagian

Perhatikan bahwa mengizinkan nilai null baik dalam kunci asing atau kolom yang direferensikan dapat menurunkan integritas referensialnya, karena kunci apa pun dengan nilai null tidak pernah diperiksa terhadap tabel yang direferensikan. Untuk menghindarinya, Anda dapat menggunakan batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_23 pada kunci asing saat membuat tabel Anda

Catatan

Kendala

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_23 tidak dapat ditambahkan ke tabel yang ada

Pencocokan kunci asing komposit

Secara default, kunci asing komposit dicocokkan menggunakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22 algoritma (yang merupakan default yang sama dengan PostgreSQL).

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21 tersedia jika ditentukan. Anda dapat menentukan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22

Semua pencocokan kunci komposit ditentukan sebelum versi 19. 1 gunakan metode perbandingan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_22. Jika Anda memiliki batasan kunci asing gabungan dan baru saja memutakhirkan ke versi 19. 1, lalu harap periksa bahwa

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22 berfungsi untuk skema Anda dan pertimbangkan untuk mengganti batasan kunci asing itu dengan yang

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21

Bagaimana itu bekerja

Untuk tujuan pencocokan, kunci asing komposit dapat berada di salah satu dari tiga status

Sah. Kunci yang dapat digunakan untuk mencocokkan hubungan kunci asing
Tidak sah. Kunci yang tidak akan digunakan untuk pencocokan (termasuk untuk operasi cascading)
Tidak dapat diterima. Kunci yang tidak dapat dimasukkan sama sekali (ditandai kesalahan)

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22 menetapkan bahwa

Kunci yang valid tidak boleh berisi nilai nol
Kunci yang tidak valid berisi satu atau lebih nilai null

Kunci yang tidak dapat diterima tidak ada dari sudut pandang

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22;

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21 menetapkan bahwa

Kunci yang valid tidak boleh berisi nilai nol
Kunci yang tidak valid harus memiliki semua nilai null

Kunci yang tidak dapat diterima memiliki kombinasi nilai null dan non-null. Dengan kata lain,

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_21 mensyaratkan bahwa jika ada kolom kunci gabungan adalah

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6, maka semua kolom kunci harus

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6

Untuk contoh yang menunjukkan cara kerja algoritme pencocokan kunci ini, lihat

Catatan

CockroachDB tidak mendukung

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

40. Untuk informasi selengkapnya, lihat edisi #20305

Tindakan kunci asing

Saat Anda menyetel batasan kunci asing, Anda dapat mengontrol apa yang terjadi pada kolom yang dibatasi saat kolom yang direferensikan (kunci asing) dihapus atau diperbarui

ParameterDescription

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

41Default action. Jika ada referensi ke kunci yang dihapus, transaksi akan gagal di akhir pernyataan. Kunci dapat diperbarui, tergantung pada tindakan ________12______42

Alias.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

43

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

44Default action. Jika ada referensi ke kunci yang sedang diperbarui, transaksi akan gagal di akhir pernyataan. Kunci dapat dihapus, tergantung pada tindakan ________12______45

Alias.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

46

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

43 /

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

46

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

49 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

50 saat ini setara sampai opsi untuk menunda pemeriksaan kendala ditambahkan. Untuk menyetel tindakan kunci asing yang ada ke

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

49, batasan kunci asing harus dihapus dan dibuat ulang.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

52 /

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

53Ketika kunci asing yang direferensikan dihapus atau diperbarui, semua baris yang mereferensikan kunci itu masing-masing dihapus atau diperbarui. Jika ada perubahan lain pada baris, seperti

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

54 atau

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

55, penghapusan akan diutamakan

Perhatikan bahwa

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_56 tidak mencantumkan objek yang dijatuhkan atau diperbarui, jadi harus digunakan dengan hati-hati.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

57 /

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

58Ketika kunci asing yang direferensikan masing-masing dihapus atau diperbarui, kolom dari semua baris yang mereferensikan kunci itu akan disetel ke

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6. Kolom harus membolehkan

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

_6 atau pembaruan ini akan gagal.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

61 /

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

62Ketika kunci asing yang direferensikan dihapus atau diperbarui, kolom dari semua baris yang mereferensikan kunci itu disetel ke nilai default untuk kolom itu

Jika nilai default untuk kolom adalah nol, atau jika tidak ada nilai default yang diberikan dan kolom tidak memiliki batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

23, ini akan memiliki efek yang sama seperti

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

57 atau

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

58. Nilai default harus tetap sesuai dengan semua batasan lainnya, seperti

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

04

Catatan

Jika kolom kunci asing memiliki beberapa batasan yang mereferensikan kolom yang sama, aksi kunci asing yang ditentukan oleh kunci asing pertama diutamakan. Sebagai contoh, lihat

Pertunjukan

Karena batasan kunci asing memerlukan pemeriksaan per-baris pada dua tabel, pernyataan yang melibatkan kunci asing atau kolom yang direferensikan membutuhkan waktu lebih lama untuk dijalankan

Untuk meningkatkan kinerja kueri, sebaiknya lakukan hal berikut

Buat indeks sekunder pada semua referensi kolom kunci asing yang belum diindeks

Untuk penyisipan massal ke tabel baru dengan kunci asing atau kolom referensi, gunakan pernyataan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

67 alih-alih

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

68

Peringatan

Menggunakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_69 akan membatalkan kunci asing tanpa pernyataan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

70

Sintaksis

Kendala kunci asing dapat didefinisikan di. Namun, jika Anda hanya ingin menerapkan batasan pada satu kolom, batasan tersebut dapat diterapkan di kolom

Catatan

Anda juga dapat menambahkan batasan

> UPDATE customers SET id = 1002 WHERE id = 1001;

_9 ke tabel yang sudah ada

Tingkat kolom

CREATETABLEtable_name(column_namecolumn_typeREFERENCESparent_table(ref_column_name)column_constraints,column_table_deftable_constraints))

ParameterDescription

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_73Nama tabel yang Anda buat.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

74Nama kolom kunci asing.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

75Tipe data kolom kunci asing.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

76Nama tabel referensi kunci asing.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

77Nama kolom referensi kunci asing

Jika Anda tidak menyertakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_77 yang ingin Anda rujuk dari

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

76, CockroachDB menggunakan kolom pertama dari kunci utama

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

76.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

81 Batasan tingkat kolom lainnya yang ingin Anda terapkan ke kolom ini.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

82Definisi untuk kolom lain dalam tabel.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

83 Batasan tingkat tabel apa pun yang ingin Anda terapkan

Contoh

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

Peringatan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

56 tidak mencantumkan objek yang dijatuhkan atau diperbarui, jadi harus digunakan dengan hati-hati

Tingkat meja

CREATETABLEtable_name(column_table_def,CONSTRAINTconstraint_nameFOREIGN KEY(fk_column_name,)REFERENCESparent_table(ref_column_name,)table_constraints)

ParameterDescription

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_73Nama tabel yang Anda buat.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

82Definisi untuk kolom tabel.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

87Nama kendala.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

88Nama kolom kunci asing.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

76Nama tabel referensi kunci asing.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

77Nama kolom referensi kunci asing

Jika Anda tidak menyertakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_74 yang ingin Anda rujuk dari

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

76, CockroachDB menggunakan kolom pertama dari kunci utama

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

76.

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

83 Batasan tingkat tabel lainnya yang ingin Anda terapkan

Contoh

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

Contoh penggunaan

Gunakan batasan kunci asing dengan tindakan default

Dalam contoh ini, kita akan membuat tabel dengan batasan kunci asing dengan default (

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

95)

Pertama, buat tabel referensi

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

Selanjutnya, buat tabel referensi

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

Mari masukkan record ke dalam setiap tabel

ikon/tombol/salin

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

pq: foreign key violation: value [1002] not found in customers@primary [id]

Penyisipan catatan kedua mengembalikan kesalahan karena pelanggan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

96 tidak ada di tabel yang direferensikan

Mari masukkan record ke tabel referensi dan coba perbarui tabel referensi

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

_

Pembaruan ke tabel yang direferensikan mengembalikan kesalahan karena

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

97 direferensikan dan default diaktifkan (

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

44). Namun,

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_99 tidak dirujuk dan dapat diperbarui

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

0

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

1

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

2

Sekarang mari kita coba menghapus baris yang direferensikan

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

3

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

_

Demikian pula, penghapusan mengembalikan kesalahan karena

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

97 direferensikan dan default diaktifkan (

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

41). Namun,

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

02 tidak dirujuk dan dapat dihapus

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

5

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

1

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

7

Gunakan Batasan Kunci Asing dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 56

Dalam contoh ini, kita akan membuat tabel dengan batasan kunci asing dengan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

53 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

52

Pertama, buat tabel referensi

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

8

Kemudian, buat tabel referensi

ikon/tombol/salin

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_9

Masukkan beberapa catatan ke dalam tabel yang direferensikan

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_0

Masukkan beberapa catatan ke dalam tabel referensi

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_1

Sekarang, mari perbarui

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_06 di tabel referensi

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_2

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_3

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_4

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_5

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_6

Ketika

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_07 telah diperbarui ke

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

08 di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

09, pembaruan disebarkan ke tabel referensi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

10

Demikian pula, penghapusan akan mengalir. Ayo hapus

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_08 dari

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

09

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_7

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_3

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_9

Mari kita periksa untuk memastikan baris di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

10 di mana

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

14 juga dihapus

ikon/tombol/salin

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_5

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_1

Gunakan Batasan Kunci Asing dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 54

Dalam contoh ini, kita akan membuat tabel dengan batasan kunci asing dengan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

58 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

57

Pertama, buat tabel referensi

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_2

Kemudian, buat tabel referensi

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_3

Masukkan beberapa catatan ke dalam tabel yang direferensikan

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_4

Masukkan beberapa catatan ke dalam tabel referensi

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_5

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_6

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_7

Sekarang, mari perbarui

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_06 di tabel referensi

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_8

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_9

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_4

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_6

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

_2

Saat

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

07 diperbarui menjadi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

08 di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

21, referensi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 diatur ke

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6

Demikian pula, penghapusan akan mengatur referensi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 ke

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6. Ayo hapus

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_26 dari

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

21

ikon/tombol/salin

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

_3

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_9

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

_5

Mari kita periksa untuk memastikan baris di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

28 di mana

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

29 diperbarui menjadi

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6

ikon/tombol/salin

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_6

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

_7

Gunakan Batasan Kunci Asing dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 55

Dalam contoh ini, kita akan membuat tabel dengan batasan

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

32 dengan batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

62 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

61

Pertama, buat tabel referensi

ikon/tombol/salin

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

_8

Kemudian, buat tabel referensi dengan nilai

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

35 untuk

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 diatur ke

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

37

ikon/tombol/salin

> INSERT INTO customers VALUES (1001, '[email protected]'), (1234, '[email protected]');

_9

Masukkan beberapa catatan ke dalam tabel yang direferensikan

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_0

Masukkan beberapa catatan ke dalam tabel referensi

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_1

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_2

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_7

Sekarang, mari perbarui

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_06 di tabel referensi

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_4

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_5

> INSERT INTO orders VALUES (1, 1002, 29.99);

_6

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_2

> INSERT INTO orders VALUES (1, 1002, 29.99);

_8

Ketika

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_07 telah diperbarui ke

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

08 di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

41, referensi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 diatur ke

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

35 (i. e. ,

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_37). Anda dapat melihat ini di baris pertama dan terakhir

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

45, dengan

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

46 dan

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 sekarang menjadi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

37

Demikian pula, penghapusan akan mengatur referensi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 ke nilai

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

35. Ayo hapus

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_26 dari

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

41

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_9

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_5

pq: foreign key violation: value [1002] not found in customers@primary [id]

_1

Mari kita periksa untuk memastikan nilai

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 yang sesuai dengan

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

54, telah diperbarui ke nilai

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

35 (i. e. ,

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_37) di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

45

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1002, 29.99);

_2

pq: foreign key violation: value [1002] not found in customers@primary [id]

_3

Jika nilai default untuk kolom

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_22 tidak disetel, dan kolom tidak memiliki batasan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

23, tindakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

62 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

61, tetapkan nilai kolom referensi ke

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6

Misalnya, mari buat tabel ________104______63 baru dan masukkan beberapa nilai

ikon/tombol/salin

pq: foreign key violation: value [1002] not found in customers@primary [id]

_4

ikon/tombol/salin

pq: foreign key violation: value [1002] not found in customers@primary [id]

_5

Kemudian kita dapat membuat tabel

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_64 baru yang mereferensikan tabel

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

63, tetapi tanpa nilai default yang ditentukan untuk tindakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

62 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

61

ikon/tombol/salin

pq: foreign key violation: value [1002] not found in customers@primary [id]

_6

ikon/tombol/salin

pq: foreign key violation: value [1002] not found in customers@primary [id]

_7

Menghapus dan memperbarui nilai dalam tabel

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

63 menetapkan nilai referensi dalam

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

64 hingga

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

6

ikon/tombol/salin

pq: foreign key violation: value [1002] not found in customers@primary [id]

_8

ikon/tombol/salin

pq: foreign key violation: value [1002] not found in customers@primary [id]

_9

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_0

> INSERT INTO orders VALUES (1, 1001, 29.99);

_1

Tambahkan beberapa batasan kunci asing ke satu kolom

Anda dapat menambahkan lebih dari satu batasan kunci asing ke satu kolom

Misalnya, jika Anda membuat tabel berikut

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_2

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_3

Anda dapat membuat tabel dengan kolom yang mereferensikan kolom di tabel

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

1 dan

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

0

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_4

Sisipan ke dalam tabel

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

73 harus memenuhi kedua batasan kunci asing pada

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 (

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

75 dan

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

76)

Mari masukkan record ke dalam setiap tabel

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_5

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_6

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_7

Pernyataan terakhir berhasil karena

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_77 cocok dengan nilai unik

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

06 dalam tabel

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

1 dan nilai unik

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 dalam tabel

pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

0. Jika

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_77 tidak ada di salah satu kolom yang direferensikan, atau hanya di salah satunya, pernyataan itu akan mengembalikan kesalahan

Misalnya, pernyataan berikut hanya memenuhi salah satu batasan kunci asing dan mengembalikan kesalahan

ikon/tombol/salin

> INSERT INTO orders VALUES (1, 1001, 29.99);

_8

> INSERT INTO orders VALUES (1, 1001, 29.99);

_9

CockroachDB memungkinkan Anda menambahkan beberapa batasan kunci asing pada kolom yang sama, yang mereferensikan kolom yang sama

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

0

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

_1

> UPDATE customers SET id = 1002 WHERE id = 1001;

_2

Sekarang ada dua batasan kunci asing pada

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

22 yang mereferensikan kolom

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

84 (i. e. ,

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_75 dan

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

76)

Jika terjadi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_87 atau

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

88 ke kolom yang direferensikan (

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

84), tindakan untuk kunci asing pertama yang ditentukan diutamakan. Dalam hal ini, itu akan menjadi default (

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

95) pada batasan kunci asing pertama (

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

75). Ini berarti bahwa

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

87s pada kolom yang direferensikan akan gagal, meskipun batasan kunci asing kedua (

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

93) ditentukan dengan tindakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

52

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

_3

> UPDATE customers SET id = 1002 WHERE id = 1001;

_4

Cocokkan kunci asing gabungan dengan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); _22 dan CREATE TABLE packages ( customer INT, "order" INT, id INT, address STRING(50), delivered BOOL, delivery_date DATE, PRIMARY KEY (customer, "order", id), CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders ); 21

Contoh di bagian ini menunjukkan cara kerja pencocokan kunci asing gabungan untuk algoritme ________12______22 dan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21. Untuk ikhtisar konseptual, lihat

Pertama, mari buat beberapa tabel.

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_99 adalah tabel dengan kunci gabungan

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

5

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_00 memiliki kunci asing di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

99 yang menggunakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21 algoritma

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

_6

> CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY,
    customer INT NOT NULL REFERENCES customers (id),
    orderTotal DECIMAL(9,2),
    INDEX (customer)
  );

_03 memiliki kunci asing di

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

99 yang menggunakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

22 algoritma (default)

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

_7

Selanjutnya, kami mengisi

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

_99 dengan beberapa nilai

ikon/tombol/salin

> UPDATE customers SET id = 1002 WHERE id = 1001;

_8

Sekarang mari kita lihat beberapa pernyataan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

_68 untuk melihat bagaimana algoritma pencocokan kunci yang berbeda bekerja

COCOK SEDERHANA

Memasukkan nilai ke dalam tabel menggunakan

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    );

21 algoritma (dijelaskan ) memberikan hasil sebagai berikut

Bisakah kunci asing menjadi beberapa kolom?

MySQL memungkinkan kita menambahkan batasan FOREIGN KEY pada beberapa kolom dalam sebuah tabel . Syaratnya adalah setiap Foreign Key pada tabel anak harus mengacu pada tabel induk yang berbeda.

Bisakah kunci asing mereferensikan banyak kolom dari tabel induk?

Tidak . Batasan kunci asing menyebutkan dengan tepat tabel dan kolom mana yang direferensikannya, dan harus mereferensikan tabel dan kolom yang sama pada setiap baris.

Bisakah kunci asing menjadi bagian dari banyak tabel?

batasan FOREIGN KEY berbeda dari batasan PRIMARY KEY, Anda hanya dapat membuat satu PRIMARY KEY per setiap tabel, dengan kemampuan untuk membuat beberapa batasan FOREIGN KEY di setiap tabel . .