Ràng buộc FOREIGN KEY – Thiết lập khóa ngoại cho bảng

Bằng cách đặt ràng buộc khóa ngoại FOREIGN KEY trên một cột, bạn có thể giới hạn giá trị được lưu trên cột đó bằng giá trị được lưu ở cột chỉ định trên một bảng khác.

Định dạng như sau:

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type REFERENCES reftable [ ( refcolumn ) ]
)

Thiết lập bảng tham chiếu (referenced table) reftable có cột tham chiếu (referenced column) refcolumn cho cột column_name muốn chỉ định ràng buộc khóa ngoại FOREIGN KEY.

Ngoài ra, ràng buộc FOREIGN KEY cũng có thể được đặt trên bảng để thiết lập ràng buộc cho một hoặc nhiều cột thay vì trên cơ sở từng cột:

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name1 data_type1, 
  column_name2 data_type2,
  [CONSTRAINT constraint_name] FOREIGN KEY ( column_name1 )
    REFERENCES reftable [ ( refcolumn ) ]
)

Cột được tham chiếu refcolumn bởi ràng buộc khóa ngoại FOREIGN KEY BẮT BUỘC PHẢI CÓ ràng buộc khóa chính PRIMARY KEY hoặc ràng buộc duy nhất UNIQUE.

※Nếu bạn không chỉ định rõ ràng tên cho ràng buộc khóa ngoại [CONSTRAINT constraint_name], PostgreSQL sẽ chỉ định tên mặc định cho ràng buộc khóa ngoại. Theo mặc định, PostgreSQL sử dụng table-name_column-name_fkey làm tên mặc định cho ràng buộc khóa ngoại (Xem hình bên dưới).

Bây giờ sẽ đi vào ví dụ cụ thể. Let’s Go!

Chúng ta sẽ tạo bảng department với vai trò là bảng được tham chiếu dữ liệu về phòng ban. Sau đó, tạo bảng nhân viên staff làm bảng tham chiếu dữ liệu về phòng ban. Sử dụng ràng buộc FOREIGN KEY cho cột liên quan đến phòng ban của bảng staff để chỉ có thể đăng ký dữ liệu được lưu trong bảng department.

Đầu tiên tạo bảng được tham chiếu có tên department trong lược đồ myschema1 của cơ sở dữ liệu mydb:

CREATE TABLE myschema1.department (
  name varchar(10) PRIMARY KEY
);

Ràng buộc khóa chính PRIMARY KEY được đặt trên cột được tham chiếu name của bảng được tham chiếu department.

và thêm dữ liệu vào bảng department:

INSERT INTO myschema1.department VALUES
  ('Sales'), 
  ('Accounting'), 
  ('Marketing');

Tiếp theo, bảng tham chiếu staff trong lược đồ myschema1 của cơ sở dữ liệu mydb:

CREATE TABLE myschema1.staff(
  id integer, 
  staffname varchar(30), 
  depname varchar(30), 
  FOREIGN KEY (depname) REFERENCES myschema1.department(name)
);

Xem thông tin cột và ràng buộc đã tạo của bảng staff :

\d myschema1.staff

Bây giờ chúng ta sẽ thêm một số dữ liệu OK vào bảng staff:

INSERT INTO myschema1.staff VALUES
  (1, 'Hoang Ngoc Minh', 'Sales'), 
  (2, 'Minh Hoang Blog', 'Marketing'), 
  (3, 'Hoang Vu Nam Anh', 'Accounting');

Vì ràng buộc khóa ngoại được đặt trên cột depname của bảng staff tham chiếu đến cột name của bảng department nên giá trị của cột depname này CHỈ CÓ THỂ thêm một trong các giá trị được lưu trong cột name của bảng department. Cả ba dữ liệu được thêm lần này vào cột depname đều có trong cột name nên đã được thêm thành công.

Tiếp theo, thêm dữ liệu vào bảng staff có chứa giá trị không được lưu trữ trong cột được tham chiếu ở bảng department:

INSERT INTO myschema1.staff VALUES
  (4, 'Vu Huynh Anh Phuong', 'Customer');

lần này thì việc thêm dữ liệu đã thất bại vì giá trị depname ‘Customer’ không tìm thấy trong cột name của bảng department.

Khi ràng buộc FOREIGN KEY được đặt, chỉ dữ liệu được lưu trữ trong bảng được tham chiếu mới có thể được thêm vào bảng tham chiếu. Ở trạng thái dữ liệu được thêm vào bảng tham chiếu, mà dữ liệu trong bảng được tham chiếu bị xóa hoặc cập nhật thì hai bảng trở nên không nhất quán.

Bạn có thể thiết lập những việc cần làm với dữ liệu trong bảng tham chiếu khi dữ liệu của bảng được tham chiếu cập nhật hoặc xóa.

Sử dụng định dạng sau:

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type REFERENCES reftable [ ( refcolumn ) ]
    [ ON DELETE delete_action ]
    [ ON UPDATE update_action ]
)

Hành động [ ON DELETE delete_action ] thiết lập hành vi khi bảng được tham chiếu bị xóa và hành động [ ON UPDATE update_action ] thiết lập hành vi khi bảng được tham chiếu được cập nhật.

Nó có thể được đặt theo cách tương tự ngay cả khi giới hạn khóa ngoại được đặt cho bảng thay vì cho mỗi cột:

CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name1 data_type1, 
  column_name2 data_type2,
  [CONSTRAINT constraint_name] FOREIGN KEY ( column_name1 )
    REFERENCES reftable [ ( refcolumn ) ]
    [ ON DELETE delete_action ]
    [ ON UPDATE update_action ]
)

Các giá trị có thể được đặt trong action cho mỗi giá trị như sau:

Chúng ta sẽ làm ví dụ với option CASCADE. Nếu dữ liệu bị xóa khỏi bảng được tham chiếu, dữ liệu đang tham chiếu cũng bị xóa. Khi dữ liệu của bảng được tham chiếu được cập nhật, dữ liệu đang tham chiếu cũng được cập nhật về cùng một giá trị.

Bảng được tham chiếu department đã tạo ở trên sẽ tiếp tục được sử dụng.

Bảng tham chiếu staff sẽ được xóa và tạo lại để thêm action với option CASCADE trong lược đồ myschema1 của cơ sở dữ liệu mydb:

DROP TABLE IF EXISTS myschema1.staff;

CREATE TABLE myschema1.staff(
  id integer, 
  staffname varchar(30), 
  depname varchar(30), 
  FOREIGN KEY (depname) REFERENCES myschema1.department(name)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

thêm lại dữ liệu vào bảng staff:

INSERT INTO myschema1.staff VALUES
  (1, 'Hoang Ngoc Minh', 'Sales'), 
  (2, 'Minh Hoang Blog', 'Marketing'), 
  (3, 'Hoang Vu Nam Anh', 'Accounting');

Bây giờ chúng ta sẽ xóa một phần dữ liệu khỏi bảng department được tham chiếu:

DELETE FROM myschema1.department
  WHERE name = 'Marketing';

Dữ liệu đã bị xóa. Vì CASCADE được chỉ định ON DELETE cho bảng tham chiếu staff, nên tất cả dữ liệu tham chiếu trên bảng staff đến giá trị bị xóa trên bảng được tham chiếu department cũng tự động bị xóa khỏi bảng staff:

SELECT * FROM myschema1.staff;

Kết quả dữ liệu đã bị xóa khỏi bảng tham chiếu staff.

Bây giờ, thử cập nhật dữ liệu được tham chiếu trong bảng department với một giá trị mới:

UPDATE myschema1.department SET name = 'Finance'
  WHERE name = 'Accounting';

Dữ liệu đã được cập nhật.

Vì CASCADE được chỉ định ON UPDATE cho bảng tham chiếu staff, nên tất cả dữ liệu tham chiếu trên bảng staff đến giá trị được cập nhật trên bảng được tham chiếu department cũng tự động được cập nhật thành cùng một giá trị trên bảng staff:

SELECT * FROM myschema1.staff;

Kết quả dữ liệu của bảng tham chiếu staff cũng đã được cập nhật thành Finance.

Để thêm ràng buộc khóa ngoại FOREIGN KEY vào bảng hiện có, bạn sử dụng mẫu sau của câu lệnh ALTER TABLE:

ALTER TABLE bảng_tham_chiếu 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (fk_columns) 
REFERENCES parent_table (parent_key_columns);

Giả sử, bạn muốn thêm ràng buộc FOREIGN KEY với tùy chọn ON DELETE CASCADE vào bảng hiện có, bạn cần làm theo các bước sau:

① Đầu tiên, loại bỏ các ràng buộc khóa ngoại hiện có:

ALTER TABLE bảng_tham_chiếu 
DROP CONSTRAINT constraint_fkey;

② Tiếp theo, thêm một ràng buộc FOREIGN KEY mới với action ON DELETE CASCADE:

ALTER TABLE bảng_tham_chiếu
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;