Một đến nhiều khóa ngoại sql

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,<
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Máy chủ SQL/Truy cập Oracle/MS

TẠO BẢNG Đơn đặt hàng (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int
);

Để cho phép đặt tên ràng buộc FOREIGN KEY và để xác định ràng buộc FOREIGN KEY trên nhiều cột, hãy sử dụng cú pháp SQL sau

Khóa chính đóng vai trò là mã định danh duy nhất cho các bản ghi trong bảng, trong khi khóa ngoại được sử dụng để liên kết các bảng có liên quan với nhau. Khi thiết kế một tập hợp các bảng cơ sở dữ liệu, điều quan trọng là chỉ định trường nào sẽ được sử dụng cho khóa chính và khóa ngoại để làm rõ cả cấu trúc trong bảng và mối quan hệ giữa các bảng

Giới thiệu về khóa ngoại

Nền tảng cơ bản của hệ thống cơ sở dữ liệu quan hệ như MySQL là khả năng tạo mối quan hệ giữa các bảng tạo nên cơ sở dữ liệu. Bằng cách cho phép dễ dàng liên kết các bản ghi trong các bảng khác nhau với nhau, RDBMS cho phép phân tích dữ liệu theo nhiều cách khác nhau đồng thời giữ cho dữ liệu được sắp xếp theo kiểu có hệ thống, với sự dư thừa tối thiểu

Các mối quan hệ này được quản lý thông qua việc sử dụng khóa ngoại, về cơ bản, các trường có cùng ý nghĩa trong tất cả các bảng trong mối quan hệ và đóng vai trò là điểm chung để liên kết các bản ghi trong các bảng khác nhau với nhau. Mối quan hệ khóa ngoại có thể là một đối một (một bản ghi trong một bảng được liên kết với một và chỉ một bản ghi trong một bảng khác) hoặc một đối nhiều (một bản ghi trong một bảng được liên kết với nhiều bản ghi trong một bảng khác)

HÌNH 1 Mối quan hệ một đối một giữa các bảng

GHI CHÚ

Khóa ngoại chỉ được hỗ trợ trên các bảng InnoDB

Hình 1 minh họa mối quan hệ một đối một. một dịch vụ và mô tả liên quan của nó, với mối quan hệ giữa hai dịch vụ được quản lý thông qua trường ServiceID duy nhất

Hình 2 minh họa mối quan hệ một-nhiều. tác giả và sách của họ, với liên kết giữa hai cuốn sách được duy trì thông qua trường AuthorID duy nhất

HÌNH 2 Mối quan hệ một-nhiều giữa các bảng

Khi tạo bảng, khóa ngoại có thể được xác định theo cách tương tự như khóa chính bằng cách sử dụng công cụ sửa đổi FOREIGN KEY…REFERENCES. Ví dụ sau minh họa bằng cách tạo hai bảng InnoDB được liên kết với nhau trong mối quan hệ một-nhiều bằng mã định danh loại máy bay

mysql> 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

Là một

Làm cách nào để tạo mối quan hệ 1 đến nhiều trong SQL?

Chúng ta có thể có nhiều hơn 1 khóa ngoại trong SQL không?

Làm cách nào để tạo mối quan hệ 1-1 trong SQL Server?