Bạn có một bảng và bạn muốn xem tất cả các bảng khác có ràng buộc khóa ngoại trỏ đến bảng đó hoặc đến một cột cụ thể trong bảng đó
Để xem các mối quan hệ khóa ngoại của một bảng
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'db_name'
AND REFERENCED_TABLE_NAME = 'table_name';
Để xem các mối quan hệ khóa ngoại của một cột
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'db_name'
AND REFERENCED_TABLE_NAME = 'table_name'
AND REFERENCED_COLUMN_NAME = 'column_name';
Bạn có thể không cần giải quyết REFERENCED_TABLE_SCHEMA
cho cơ sở dữ liệu hiện tại mà bạn đang mở
Cần một Công cụ GUI tốt cho MySQL? .
Khóa ngoại là một ràng buộc có thể được sử dụng để thực thi tính toàn vẹn của dữ liệu. Nó được tạo bởi một cột [hoặc một tập hợp các cột] trong một bảng được gọi là bảng con, tham chiếu đến một cột [hoặc một tập hợp các cột] trong một bảng được gọi là bảng cha. Nếu khóa ngoại được sử dụng, MariaDB sẽ thực hiện một số kiểm tra để đảm bảo rằng một số quy tắc toàn vẹn luôn được thực thi. Để có giải thích đầy đủ hơn, hãy xem Cơ sở dữ liệu quan hệ. Khóa ngoại
Khóa ngoại chỉ có thể được sử dụng với các công cụ lưu trữ hỗ trợ chúng. InnoDB mặc định và PBXT lỗi thời hỗ trợ khóa ngoại
Các bảng được phân vùng không thể chứa khóa ngoại và không thể được tham chiếu bằng khóa ngoại
cú pháp
Ghi chú. Cho đến khi MariaDB 10. 4, MariaDB chấp nhận định dạng lối tắt với mệnh đề REFERENCES chỉ trong các câu lệnh ALTER TABLE và CREATE TABLE, nhưng cú pháp đó không có tác dụng gì. Ví dụ
CREATE TABLE b[for_key INT REFERENCES a[not_key]];
MariaDB chỉ cần phân tích cú pháp mà không trả lại bất kỳ lỗi hoặc cảnh báo nào, để tương thích với các DBMS khác. Tuy nhiên, chỉ cú pháp được mô tả bên dưới mới tạo khóa ngoại
Từ MariaDB 10. 5, MariaDB sẽ cố gắng áp dụng ràng buộc. Xem bên dưới
Khóa ngoại được tạo bằng CREATE TABLE hoặc ALTER TABLE. Định nghĩa phải tuân theo cú pháp này
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
Mệnh đề symbol
, nếu được chỉ định, được sử dụng trong thông báo lỗi và phải là duy nhất trong cơ sở dữ liệu
Các cột trong bảng con phải là chỉ mục BTREE [không phải HASH, RTREE hoặc FULLTEXT — xem SHOW INDEX] hoặc phần ngoài cùng bên trái của chỉ mục BTREE. Tiền tố chỉ mục không được hỗ trợ [do đó, không thể sử dụng các cột TEXT và BLOB làm khóa ngoại]. Nếu MariaDB tự động tạo một chỉ mục cho khóa ngoại [vì nó không tồn tại và không được tạo rõ ràng], tên của nó sẽ là
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT0
Các cột được tham chiếu trong bảng cha phải là một chỉ mục hoặc tiền tố của một chỉ mục
Các cột khóa ngoại và các cột được tham chiếu phải cùng loại hoặc các loại tương tự. Đối với kiểu số nguyên thì kích thước và dấu cũng phải giống nhau
Cả cột khóa ngoại và cột được tham chiếu đều có thể là cột LIÊN TỤC. Tuy nhiên, các mệnh đề ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL không được phép trong trường hợp này
Bảng cha và bảng con phải sử dụng cùng một công cụ lưu trữ và không được
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT1 hoặc các bảng được phân vùng. Họ có thể là cùng một bảng
Hạn chế
Nếu có khóa ngoại, mỗi hàng trong bảng con phải khớp với một hàng trong bảng cha. Nhiều hàng con có thể khớp với cùng một hàng cha. Hàng con khớp với hàng cha nếu tất cả các giá trị khóa ngoại của nó giống hệt với giá trị của hàng cha trong bảng cha. Tuy nhiên, nếu ít nhất một trong các giá trị khóa ngoại là
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT2, thì hàng không có cha nhưng vẫn được phép
MariaDB thực hiện một số kiểm tra nhất định để đảm bảo rằng tính toàn vẹn của dữ liệu được thực thi
- Việc cố gắng chèn các hàng không khớp [hoặc cập nhật các hàng khớp theo cách khiến chúng không khớp với các hàng] trong bảng con sẽ tạo ra lỗi 1452 [SQLSTATE '23000']
- Khi một hàng trong bảng cha bị xóa và tồn tại ít nhất một hàng con, MariaDB sẽ thực hiện một hành động phụ thuộc vào mệnh đề
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
3 của khóa ngoại - Khi một giá trị trong cột được tham chiếu bởi khóa ngoại thay đổi và tồn tại ít nhất một hàng con, MariaDB sẽ thực hiện một hành động phụ thuộc vào mệnh đề
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
4 của khóa ngoại - Cố gắng loại bỏ một bảng được tham chiếu bởi khóa ngoại sẽ tạo ra lỗi 1217 [SQLSTATE '23000']
- BẢNG TRUNCATE đối với bảng chứa một hoặc nhiều khóa ngoại được thực thi dưới dạng XÓA mà không có WHERE, để các khóa ngoại được thực thi cho mỗi hàng
Các hành động được phép cho
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT3 và
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT4 là
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
7. Thay đổi trên bảng cha bị ngăn chặn. Câu lệnh kết thúc với lỗi 1451 [SQLSTATE '2300']. Đây là hành vi mặc định cho cả[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
3 và[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
4CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
0. Từ đồng nghĩa với[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
7CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
2. Thay đổi được cho phép và lan truyền trên bảng con. Ví dụ: nếu một hàng cha bị xóa, hàng con cũng bị xóa;CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
3. Thay đổi được cho phép và các cột khóa ngoại của hàng con được đặt thành[CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_col_name, ...] REFERENCES tbl_name [index_col_name,...] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
2CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
5. Chỉ làm việc với PBXT. Tương tự nhưCREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
3, nhưng các cột khóa ngoại được đặt thành giá trị mặc định của chúng. Nếu các giá trị mặc định không tồn tại, một lỗi sẽ được tạo ra
Thao tác xóa hoặc cập nhật được kích hoạt bởi khóa ngoại không kích hoạt trình kích hoạt và không được tính trong biến trạng thái và
Các ràng buộc khóa ngoại có thể bị vô hiệu hóa bằng cách đặt biến hệ thống máy chủ thành 0. Điều này tăng tốc độ chèn một lượng lớn dữ liệu
Bảng Information Schema
CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;7 chứa thông tin về khóa ngoại. Các cột riêng lẻ được liệt kê trong bảng
CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;8
Các bảng Lược đồ thông tin dành riêng cho InnoDB cũng chứa thông tin về các khóa ngoại của InnoDB. Thông tin khóa ngoại được lưu trữ trong
CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;9. Dữ liệu về các cột riêng lẻ được lưu trữ trong
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]0
Cách dễ đọc nhất để lấy thông tin về khóa ngoại của bảng đôi khi là câu lệnh
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]1
Hạn chế
Khóa ngoại có các giới hạn sau trong MariaDB
- Hiện tại, khóa ngoại chỉ được hỗ trợ bởi InnoDB
- Không thể được sử dụng với chế độ xem
- Hành động
CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
5 không được hỗ trợ - Hành động khóa ngoại không kích hoạt trình kích hoạt
- Nếu BẬT CẬP NHẬT CASCADE lặp lại để cập nhật cùng một bảng mà nó đã cập nhật trước đó trong quá trình xếp tầng, nó hoạt động như HẠN CHẾ
ví dụ
Hãy xem một ví dụ. Chúng ta sẽ tạo một bảng
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]3 và một bảng
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]4. Cả hai bảng đều có khóa chính được gọi là
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]5.
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]4 cũng có một khóa ngoại bao gồm một trường có tên là
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]7, dùng để chỉ khóa chính của
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]3. Tên ràng buộc khóa ngoại là tùy chọn, nhưng chúng tôi sẽ chỉ định nó vì chúng tôi muốn nó xuất hiện trong thông báo lỗi.
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]9
CREATE TABLE author [ id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR[100] NOT NULL ] ENGINE = InnoDB; CREATE TABLE book [ id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR[200] NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT `fk_book_author` FOREIGN KEY [author_id] REFERENCES author [id] ON DELETE CASCADE ON UPDATE RESTRICT ] ENGINE = InnoDB;
Bây giờ, nếu chúng tôi cố gắng chèn một cuốn sách có tác giả không tồn tại, chúng tôi sẽ gặp lỗi
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]
Lỗi rất mô tả
Bây giờ, hãy cố gắng chèn đúng hai tác giả và sách của họ
INSERT INTO author [name] VALUES ['Abdul Alhazred']; INSERT INTO book [title, author_id] VALUES ['Necronomicon', LAST_INSERT_ID[]]; INSERT INTO author [name] VALUES ['H.P. Lovecraft']; INSERT INTO book [title, author_id] VALUES ['The call of Cthulhu', LAST_INSERT_ID[]], ['The colour out of space', LAST_INSERT_ID[]];
Nó đã làm việc
Bây giờ, hãy xóa tác giả thứ hai. Khi chúng tôi tạo khóa ngoại, chúng tôi đã chỉ định
INSERT INTO author [name] VALUES ['Abdul Alhazred']; INSERT INTO book [title, author_id] VALUES ['Necronomicon', LAST_INSERT_ID[]]; INSERT INTO author [name] VALUES ['H.P. Lovecraft']; INSERT INTO book [title, author_id] VALUES ['The call of Cthulhu', LAST_INSERT_ID[]], ['The colour out of space', LAST_INSERT_ID[]];0. Điều này sẽ tuyên truyền việc xóa và làm cho sách của tác giả bị xóa biến mất
DELETE FROM author WHERE name = 'H.P. Lovecraft'; SELECT * FROM book; +----+--------------+-----------+ | id | title | author_id | +----+--------------+-----------+ | 3 | Necronomicon | 1 | +----+--------------+-----------+
Chúng tôi cũng chỉ định
INSERT INTO author [name] VALUES ['Abdul Alhazred']; INSERT INTO book [title, author_id] VALUES ['Necronomicon', LAST_INSERT_ID[]]; INSERT INTO author [name] VALUES ['H.P. Lovecraft']; INSERT INTO book [title, author_id] VALUES ['The call of Cthulhu', LAST_INSERT_ID[]], ['The colour out of space', LAST_INSERT_ID[]];1. Điều này sẽ ngăn chúng tôi sửa đổi
INSERT INTO book [title, author_id] VALUES ['Necronomicon', 1]; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY [`author_id`] REFERENCES `author` [`id`] ON DELETE CASCADE]5 của tác giả [cột được tham chiếu bởi khóa ngoại] nếu tồn tại một hàng con