KHÓA NGOÀI là một trường [hoặc tập hợp các trường] trong một bảng tham chiếu đến KHÓA CHÍNH trong một bảng khác
SQL NGOẠI KHÓA trên TẠO BẢNG
SQL sau tạo một FOREIGN KEY trên cột "PersonID" khi tạo bảng "Orders"
mysql
TẠO BẢNG Đơn đặt hàng [
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int, CREATE TABLE aircrafttype [
-> AircraftTypeID smallint[4] unsigned NOT NULL AUTO_INCREMENT,
-> AircraftName varchar[255] NOT NULL,
-> PRIMARY KEY [AircraftTypeID]
-> ] ENGINE=INNODB;
Query OK, 0 rows affected [0.61 sec]
mysql> CREATE TABLE aircraft [
-> AircraftID smallint[4] unsigned NOT NULL AUTO_INCREMENT,
-> AircraftTypeID smallint[4] unsigned NOT NULL,
-> RegNum char[6] NOT NULL,
-> LastMaintEnd date NOT NULL,
-> NextMaintBegin date NOT NULL,
-> NextMaintEnd date NOT NULL,
-> PRIMARY KEY [AircraftID],
-> UNIQUE RegNum [RegNum],
-> INDEX [AircraftTypeID],
-> FOREIGN KEY [AircraftTypeID]
-> REFERENCES aircrafttype [AircraftTypeID]
-> ] ENGINE=INNODB;
Query OK, 0 rows affected [0.45 sec]
Trong ví dụ này, máy bay. Trường AircraftTypeID là khóa ngoại được liên kết với loại máy bay. Khóa chính AircraftTypeID. Lưu ý cách thức mà mối quan hệ này được chỉ định trong công cụ sửa đổi FOREIGN KEY…REFERENCES. Phần FOREIGN KEY chỉ định một đầu mối quan hệ [tên trường trong bảng hiện tại], trong khi phần THAM KHẢO chỉ định đầu kia của mối quan hệ [tên trường trong bảng được tham chiếu]
TIỀN BOA
Theo nguyên tắc chung, bạn nên sử dụng các trường số nguyên làm khóa ngoại thay vì các trường ký tự, vì điều này tạo ra hiệu suất tốt hơn khi nối các bảng
Khi khóa ngoại được thiết lập, MySQL chỉ cho phép nhập các giá trị đó vào các loại máy bay vào bảng máy bay cũng tồn tại trong bảng loại máy bay. Tiếp tục ví dụ trước, hãy xem cách nó hoạt động
mysql> INSERT INTO aircrafttype -> [AircraftTypeID, AircraftName] -> VALUES [503, 'Boeing 747']; Query OK, 1 row affected [0.09 sec] mysql> INSERT INTO aircraft -> [AircraftID, AircraftTypeID, RegNum, -> LastMaintEnd, NextMaintBegin, NextMaintEnd] -> VALUES -> [3451, 503, 'ZX6488', -> '2007-10-01', '2008-10-23', '2008-10-31']; Query OK, 1 row affected [0.04 sec] mysql> INSERT INTO aircraft -> [AircraftID, AircraftTypeID, RegNum, -> LastMaintEnd, NextMaintBegin, NextMaintEnd] -> VALUES -> [3452, 616, 'ZX6488', -> '2007-10-01', '2008-10-23', '2008-10-31']; ERROR 1452 [23000]: Cannot add or update a child row: a foreign key constraint fails [`db1`.`aircraft`, CONSTRAINT `aircraft_ibfk_1` FOREIGN KEY [`AircraftTypeID`] REFERENCES `aircrafttype` [`AircraftTypeID`]]
Do đó, vì loại máy bay có mã định danh 616 không tồn tại trong loại máy bay, MySQL từ chối bản ghi có giá trị đó cho bảng máy bay. Theo cách này, các ràng buộc khóa ngoại có thể giúp ích đáng kể trong việc thực thi tính toàn vẹn dữ liệu của các bảng trong cơ sở dữ liệu và giảm sự xuất hiện của các giá trị trường “xấu” hoặc không nhất quán
Ba ràng buộc sau phải được ghi nhớ khi liên kết các bảng với khóa ngoại
- Tất cả các bảng trong mối quan hệ phải là bảng InnoDB. Trong các bảng không phải của InnoDB, công cụ sửa đổi FOREIGN KEY…REFERENCES đơn giản bị MySQL bỏ qua
- Các trường được sử dụng trong mối quan hệ khóa ngoài phải được lập chỉ mục trong tất cả các bảng được tham chiếu [InnoDB sẽ tự động tạo các chỉ mục này cho bạn nếu bạn không chỉ định bất kỳ bảng nào]
- Kiểu dữ liệu của các trường có tên trong mối quan hệ khóa ngoại phải giống nhau. Điều này đặc biệt đúng với các kiểu số nguyên, chúng phải khớp nhau cả về kích thước và dấu hiệu.
Điều thú vị cần lưu ý là đây. Ngay cả khi các ràng buộc khóa ngoại tồn tại trên một bảng, MySQL vẫn cho phép bạn DROP bảng mà không gây ra lỗi [ngay cả khi làm như vậy sẽ phá vỡ các mối quan hệ khóa ngoại đã thiết lập trước đó]. Trên thực tế, trong các phiên bản MySQL trước 4. 0. 13, thả bàn là cách duy nhất để xóa khóa ngoại. mysql 4. 0. Tuy nhiên, phiên bản 13 trở lên hỗ trợ một cách ít quyết liệt hơn để xóa khóa ngoại khỏi bảng, thông qua lệnh ALTER TABLE. Đây là một ví dụ
mysql> ALTER TABLE aircraft DROP FOREIGN KEY aircraft_ibfk_1; Query OK, 1 row affected [0.57 sec] Records: 1 Duplicates: 0 Warnings: 0
Để xóa tham chiếu khóa ngoại, hãy sử dụng mệnh đề DROP FOREIGN KEY với tên nội bộ của ràng buộc khóa ngoại. Tên nội bộ này có thể được lấy bằng cách sử dụng câu lệnh SHOW CREATE TABLE. Và trong trường hợp bạn thắc mắc tại sao bạn phải sử dụng tên ràng buộc bên trong mà không phải tên trường trong mệnh đề DROP FOREIGN KEY… thì, đó là một câu hỏi hay
Cập nhật và xóa khóa tự động
Khóa ngoại chắc chắn có thể đảm bảo tính toàn vẹn của các bản ghi mới được chèn. Nhưng nếu một bản ghi bị xóa khỏi bảng có tên trong mệnh đề REFERENCES thì sao?
Rõ ràng, những bản ghi đó cũng nên bị xóa, nếu không bạn sẽ có những bản ghi mồ côi làm lộn xộn cơ sở dữ liệu của mình. Mysql 3. 23. 50 trở lên đơn giản hóa tác vụ này bằng cách cho phép bạn thêm mệnh đề BẬT XÓA vào công cụ sửa đổi FOREIGN KEY…REFERENCES, cho phép cơ sở dữ liệu biết phải làm gì với các bản ghi mồ côi trong tình huống như vậy. Đây là một trình tự chứng minh điều này
mysql> CREATE TABLE aircraft [ -> AircraftID smallint[4] unsigned NOT NULL AUTO_INCREMENT, -> AircraftTypeID smallint[4] unsigned NOT NULL, -> RegNum char[6] NOT NULL, -> LastMaintEnd date NOT NULL, -> NextMaintBegin date NOT NULL, -> NextMaintEnd date NOT NULL, -> PRIMARY KEY [AircraftID], -> UNIQUE RegNum [RegNum], -> FOREIGN KEY [AircraftTypeID] -> REFERENCES aircrafttype [AircraftTypeID] -> ON DELETE CASCADE -> ] ENGINE=INNODB; Query OK, 0 rows affected [0.17 sec] mysql> INSERT INTO aircraft -> [AircraftID, AircraftTypeID, RegNum, -> LastMaintEnd, NextMaintBegin, NextMaintEnd] -> VALUES -> [3451, 503, 'ZX6488', -> '2007-10-01', '2008-10-23', '2008-10-31']; Query OK, 1 row affected [0.05 sec] mysql> DELETE FROM aircrafttype; Query OK, 1 row affected [0.06 sec] mysql> SELECT * FROM aircraft; Empty set [0.01 sec]
mysql 4. 0. 8 trở lên cũng cho phép bạn thực hiện các hành động tự động này trên các bản cập nhật bằng cách cho phép sử dụng mệnh đề BẬT CẬP NHẬT, hoạt động theo cách tương tự như mệnh đề BẬT XÓA. Vì vậy, ví dụ: việc thêm mệnh đề BẬT CẬP NHẬT CASCADE vào định nghĩa khóa ngoại cho MySQL biết rằng khi một bản ghi được cập nhật trong bảng chính [bảng được tham chiếu để kiểm tra khóa ngoại], tất cả các bản ghi sử dụng giá trị khóa ngoại đó trong bảng hiện tại sẽ
Bảng 1 liệt kê bốn từ khóa có thể tuân theo mệnh đề BẬT XÓA hoặc BẬT CẬP NHẬT
BẢNG 1 Các hành động khả dụng trong mệnh đề ON DELETE và ON UPDATE
THẬN TRỌNG
Xin lưu ý rằng việc thiết lập MySQL cho các hoạt động tự động thông qua các quy tắc BẬT CẬP NHẬT và BẬT XÓA có thể dẫn đến hỏng dữ liệu nghiêm trọng nếu các mối quan hệ chính của bạn không được thiết lập hoàn hảo. Ví dụ: nếu bạn có một loạt bảng được liên kết với nhau bằng mối quan hệ khóa ngoại và quy tắc BẬT DELETE CASCADE, thì một thay đổi trong bất kỳ bảng chính nào cũng có thể dẫn đến bản ghi, thậm chí cả bản ghi chỉ được liên kết ngoại vi với lần xóa ban đầu, bị xóa mà không có . Vì lý do này, bạn nên kiểm tra [và sau đó kiểm tra lại] các quy tắc này trước khi hoàn thiện chúng