GALAXY TECH
Chuyên gia Công nghệ
Thứ ba, 20/10/2020 | 00:00 GMT+7

Cách cập nhật dữ liệu trong SQL

Khi làm việc với database , có thể có lúc bạn cần thay đổi dữ liệu đã được chèn vào đó. Ví dụ: bạn có thể cần sửa một mục nhập sai chính tả hoặc có lẽ bạn có thông tin mới để thêm vào một bản ghi chưa hoàn chỉnh. Ngôn ngữ truy vấn có cấu trúc - thường được gọi là SQL - cung cấp từ khóa UPDATE cho phép user thay đổi dữ liệu hiện có trong bảng.

Hướng dẫn này phác thảo cách bạn có thể sử dụng cú pháp UPDATE của SQL để thay đổi dữ liệu trong một hoặc nhiều bảng. Nó cũng giải thích cách SQL xử lý các hoạt động UPDATE xung đột với các ràng buộc foreign keys .

Yêu cầu

Để làm theo hướng dẫn này, bạn cần một máy tính chạy một số loại hệ thống quản lý database quan hệ (RDBMS) sử dụng SQL. Các hướng dẫn và ví dụ trong hướng dẫn này đã được kiểm nghiệm bằng cách sử dụng môi trường sau:

Lưu ý : Xin lưu ý nhiều RDBMS sử dụng các triển khai SQL duy nhất của riêng họ. Mặc dù các lệnh được nêu trong hướng dẫn này sẽ hoạt động trên hầu hết các RDBMS, nhưng cú pháp hoặc kết quả chính xác có thể khác nếu bạn kiểm tra chúng trên một hệ thống không phải MySQL.

  • Bạn cũng cần một database với một số bảng được tải với dữ liệu mẫu mà bạn có thể sử dụng để thực hành cập nhật dữ liệu SQL. Ta khuyến khích bạn xem qua phần Kết nối với MySQL và Cài đặt Database Mẫu sau đây để biết chi tiết về cách kết nối với server MySQL và tạo database thử nghiệm được sử dụng trong các ví dụ xuyên suốt hướng dẫn này.

Kết nối với MySQL và cài đặt database mẫu

Nếu hệ thống database SQL của bạn chạy trên một server từ xa, hãy SSH vào server của bạn từ máy local của bạn:

  • ssh sammy@your_server_ip

Sau đó, mở dấu nhắc server MySQL, thay thế sammy bằng tên account user MySQL của bạn:

  • mysql -u sammy -p

Tạo database có tên updateDB :

  • CREATE DATABASE updateDB;

Nếu database được tạo thành công, bạn sẽ nhận được kết quả như sau:

Output
Query OK, 1 row affected (0.01 sec)

Để chọn database updateDB , hãy chạy câu lệnh USE sau:

  • USE updateDB; 
Output
Database changed

Sau khi chọn database updateDB , hãy tạo một vài bảng bên trong nó. Đối với các ví dụ được sử dụng trong hướng dẫn này, hãy tưởng tượng rằng bạn điều hành một đại lý tài năng và đã quyết định bắt đầu theo dõi khách hàng của bạn và hiệu suất của họ trong database SQL. Bạn dự định bắt đầu với hai bảng, bảng đầu tiên sẽ lưu trữ thông tin về khách hàng của bạn. Bạn quyết định bảng này cần bốn cột:

  • clientID : số nhận dạng của mỗi khách hàng, được thể hiện bằng kiểu dữ liệu int . Cột này cũng sẽ đóng role là khóa chính của bảng, với mỗi giá trị hoạt động như một mã định danh duy nhất cho hàng tương ứng của nó
  • name : name mỗi khách hàng, được thể hiện bằng kiểu dữ liệu varchar với tối đa 20 ký tự
  • routine : mô tả ngắn gọn về thể loại hiệu suất chính của từng khách hàng, được thể hiện bằng cách sử dụng kiểu dữ liệu varchar nhưng với tối đa 30 ký tự
  • performanceFee : cột để ghi lại phí thực hiện tiêu chuẩn của mỗi khách hàng, nó sử dụng kiểu dữ liệu decimal với bất kỳ giá trị nào trong cột này được giới hạn ở độ dài tối đa là năm chữ số với hai trong số đó ở bên phải dấu thập phân. Do đó, phạm vi giá trị được phép trong cột này từ -999.99 đến 999.99

Tạo một bảng có tên clients có bốn cột sau:

  • CREATE TABLE clients 
  • (clientID int PRIMARY KEY, 
  • name varchar(20), 
  • routine varchar(30), 
  • standardFee decimal (5,2) 
  • );

Bảng thứ hai sẽ lưu trữ thông tin về buổi biểu diễn của khách hàng của bạn tại một địa điểm local . Bạn quyết định bảng này cần năm cột:

  • showID : tương tự như cột clientID , cột này sẽ chứa một số nhận dạng duy nhất cho mỗi chương trình, được thể hiện bằng kiểu dữ liệu int . Tương tự, cột này sẽ đóng role là khóa chính cho bảng shows
  • showDate : ngày của mỗi buổi biểu diễn. Giá trị của cột này được biểu thị bằng kiểu dữ liệu date sử dụng định dạng 'YYYY-MM-DD'
  • clientID : số ID của khách hàng biểu diễn tại triển lãm, được biểu thị dưới dạng số nguyên
  • attendance : số lượng người tham dự tại mỗi buổi biểu diễn, được biểu thị bằng số nguyên
  • ticketPrice : giá của một vé riêng lẻ tại mỗi buổi biểu diễn. Cột này sử dụng kiểu dữ liệu decimal với bất kỳ giá trị nào trong cột này được giới hạn ở độ dài tối đa là bốn chữ số với hai trong số các chữ số đó ở bên phải dấu thập phân, vì vậy phạm vi giá trị được phép trong cột này là -99.99 đến 99.99

Để đảm bảo cột clientID chỉ chứa các giá trị đại diện cho số ID khách hàng hợp lệ, bạn quyết định áp dụng ràng buộc khóa ngoại cho cột clientID tham chiếu đến cột clientID của bảng clients . Ràng buộc foreign keys là một cách để thể hiện mối quan hệ giữa hai bảng bằng cách yêu cầu các giá trị trong cột mà nó áp dụng phải tồn tại trong cột mà nó tham chiếu. Trong ví dụ sau, ràng buộc FOREIGN KEY yêu cầu bất kỳ giá trị nào được thêm vào cột clientID trong bảng shows phải tồn tại trong cột clientID của bảng client .

Tạo một bảng có tên clients có năm cột sau:

  • CREATE TABLE shows 
  • (showID int PRIMARY KEY, 
  • showDate date, 
  • clientID int, 
  • attendance int, 
  • ticketPrice decimal (4,2), 
  • CONSTRAINT client_fk 
  • FOREIGN KEY (clientID) 
  • REFERENCES clients(clientID) 
  • );

Lưu ý ví dụ này cung cấp tên cho ràng buộc foreign keys : client_fk . MySQL sẽ tự động tạo tên cho bất kỳ ràng buộc nào bạn thêm vào, nhưng việc xác định một ràng buộc ở đây sẽ hữu ích khi ta cần tham chiếu đến ràng buộc này sau này.

Tiếp theo, chạy INSERT INTO sau để tải bảng clients với năm hàng dữ liệu mẫu:

  • INSERT INTO clients 
  • VALUES 
  • (1, 'Gladys', 'song and dance', 180), 
  • (2, 'Catherine', 'standup', 99.99), 
  • (3, 'Georgeanna', 'standup', 45), 
  • (4, 'Wanda', 'song and dance', 200), 
  • (5, 'Ann', 'trained squirrel', 79.99);

Sau đó, chạy một INSERT INTO để tải bảng shows với mười hàng dữ liệu mẫu:

  • INSERT INTO shows 
  • VALUES 
  • (1, '2019-12-25', 4, 124, 15), 
  • (2, '2020-01-11', 5, 84, 29.50), 
  • (3, '2020-01-17', 3, 170, 12.99), 
  • (4, '2020-01-31', 5, 234, 14.99), 
  • (5, '2020-02-08', 1, 86, 25), 
  • (6, '2020-02-14', 3, 102, 39.5), 
  • (7, '2020-02-15', 2, 101, 26.50), 
  • (8, '2020-02-27', 2, 186, 19.99), 
  • (9, '2020-03-06', 4, 202, 30), 
  • (10, '2020-03-07', 5, 250, 8.99);

Như vậy, bạn đã sẵn sàng làm theo phần còn lại của hướng dẫn và bắt đầu tìm hiểu về cách cập nhật dữ liệu với SQL.

Cập nhật dữ liệu trong một bảng

Cú pháp chung của câu lệnh UPDATE trông giống như sau:

  • UPDATE table_name 
  • SET column_name = value_expression 
  • WHERE conditions_apply;

Theo sau từ khóa UPDATE là tên của bảng lưu trữ dữ liệu bạn muốn cập nhật. Sau đó là mệnh đề SET chỉ định dữ liệu của cột nào nên được cập nhật và cách thức. Hãy coi mệnh đề SET như việc đặt các giá trị trong cột được chỉ định bằng với bất kỳ biểu thức giá trị nào bạn cung cấp.

Trong SQL, một biểu thức giá trị - đôi khi được gọi là biểu thức vô hướng - là bất kỳ biểu thức nào sẽ trả về một giá trị duy nhất cho mọi hàng được cập nhật. Đây có thể là một chuỗi ký tự hoặc một phép toán được thực hiện trên các giá trị số hiện có trong cột. Bạn phải bao gồm ít nhất một phép gán giá trị trong mỗi câu lệnh UPDATE , nhưng bạn có thể bao gồm nhiều lần gán giá trị để cập nhật dữ liệu trong nhiều cột.

Sau mệnh đề SET là WHERE . Bao gồm WHERE trong câu lệnh UPDATE như trong cú pháp ví dụ này cho phép bạn lọc ra bất kỳ hàng nào mà bạn không muốn cập nhật. WHERE là hoàn toàn tùy chọn trong các câu lệnh UPDATE , nhưng nếu bạn không bao gồm WHERE đề này thì thao tác sẽ cập nhật mọi hàng trong bảng.

Để minh họa cách SQL xử lý các hoạt động UPDATE , hãy bắt đầu bằng cách xem xét tất cả dữ liệu trong bảng clients . Truy vấn sau đây bao gồm dấu hoa thị ( * ) là chữ viết tắt của SQL đại diện cho mọi cột trong bảng, vì vậy truy vấn này sẽ trả về tất cả dữ liệu từ mọi cột trong bảng clients :

  • SELECT * FROM clients; 
Output
+----------+------------+------------------+-------------+ | clientID | name       | routine          | standardFee | +----------+------------+------------------+-------------+ |        1 | Gladys     | song and dance   |      180.00 | |        2 | Catherine  | standup          |       99.99 | |        3 | Georgeanna | standup          |       45.00 | |        4 | Wanda      | song and dance   |      200.00 | |        5 | Ann        | trained squirrel |       79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

Ví dụ: giả sử bạn nhận thấy tên của Katherine bị sai chính tả - nó phải bắt đầu bằng chữ “K” nhưng trong bảng nó bắt đầu bằng chữ “C” - vì vậy bạn quyết định thay đổi giá trị đó bằng cách chạy câu lệnh UPDATE sau. Thao tác này cập nhật các giá trị trong cột name bằng cách thay đổi giá trị name của bất kỳ hàng nào có tên Catherine thành Katherine :

  • UPDATE clients 
  • SET name = 'Katherine' 
  • WHERE name = 'Catherine'; 
Output
Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0

Đầu ra này cho biết chỉ có một hàng được cập nhật. Bạn có thể xác nhận điều này bằng cách chạy lại truy vấn SELECT trước đó:

  • SELECT * FROM clients; 
Output
+----------+------------+------------------+-------------+ | clientID | name       | routine          | standardFee | +----------+------------+------------------+-------------+ |        1 | Gladys     | song and dance   |      180.00 | |        2 | Katherine  | standup          |       99.99 | |        3 | Georgeanna | standup          |       45.00 | |        4 | Wanda      | song and dance   |      200.00 | |        5 | Ann        | trained squirrel |       79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

Kết quả này cho thấy giá trị mà Catherine đọc trước đây thực sự đã được đổi thành Katherine .

Ví dụ này chỉ cập nhật một giá trị trong cột name . Tuy nhiên, bạn có thể cập nhật nhiều giá trị bằng WHERE ít độc quyền hơn.

Để minh họa, hãy tưởng tượng rằng bạn thương lượng phí biểu diễn tiêu chuẩn cho tất cả khách hàng của bạn , những người biểu diễn các vở hài kịch độc lập hoặc các bài hát và khiêu vũ. Câu lệnh sau sẽ cập nhật các giá trị trong cột standardFee bằng cách đặt chúng bằng 140 .

Lưu ý WHERE của ví dụ này bao gồm toán tử LIKE vì vậy nó chỉ cập nhật giá trị performanceFee cho mỗi client có giá trị routine trùng với mẫu ký tự đại diện được chỉ định, 's%' . Nói cách khác, nó sẽ cập nhật phí biểu diễn của bất kỳ người biểu diễn nào có thói quen bắt đầu bằng chữ cái “s”:

  • UPDATE clients 
  • SET standardFee = 140 
  • WHERE routine LIKE 's%'; 
Output
Query OK, 4 rows affected (0.00 sec) Rows matched: 4  Changed: 4  Warnings: 0

Nếu bạn truy vấn lại nội dung của bảng clients , tập hợp kết quả sẽ xác nhận bốn khách hàng của bạn hiện có cùng mức phí thực hiện:

  • SELECT * FROM clients; 
Output
+----------+------------+------------------+-------------+ | clientID | name       | routine          | standardFee | +----------+------------+------------------+-------------+ |        1 | Gladys     | song and dance   |      140.00 | |        2 | Katherine  | standup          |      140.00 | |        3 | Georgeanna | standup          |      140.00 | |        4 | Wanda      | song and dance   |      140.00 | |        5 | Ann        | trained squirrel |       79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

Nếu bất kỳ cột nào trong bảng của bạn chứa các giá trị số, bạn có thể cập nhật chúng bằng cách sử dụng một phép toán số học trong mệnh đề SET . Để minh họa, hãy nói rằng bạn cũng thương lượng mức tăng bốn mươi phần trăm cho mỗi khoản phí thực hiện của khách hàng của bạn. Để phản ánh điều này trong bảng clients , bạn có thể chạy thao tác UPDATE như sau:

  • UPDATE clients 
  • SET standardFee = standardFee * 1.4; 
Output
Query OK, 5 rows affected, 1 warning (0.00 sec) Rows matched: 5  Changed: 5  Warnings: 1

Lưu ý : Lưu ý kết quả này cho biết bản cập nhật dẫn đến cảnh báo. Thông thường, MySQL sẽ đưa ra cảnh báo khi nó buộc phải thực hiện thay đổi đối với dữ liệu do định nghĩa của cột hoặc bảng.

MySQL cung cấp phím tắt SHOW WARNINGS có thể giúp giải thích bất kỳ cảnh báo nào bạn nhận được:

  • SHOW WARNINGS; 
Output
+-------+------+--------------------------------------------------+ | Level | Code | Message                                          | +-------+------+--------------------------------------------------+ | Note  | 1265 | Data truncated for column 'standardFee' at row 5 | +-------+------+--------------------------------------------------+ 1 row in set (0.00 sec)

Đầu ra này thông báo cho ta biết rằng hệ thống database đã đưa ra cảnh báo vì nó phải cắt bớt một trong các giá trị standardFee mới để nó tuân theo định dạng decimal - năm chữ số với hai ở bên phải dấu thập phân - được định nghĩa đó.

Truy vấn bảng clients để xác nhận mỗi khoản phí thực hiện của mỗi khách hàng đã tăng lên bốn mươi phần trăm.

  • SELECT * FROM clients; 
Output
+----------+------------+------------------+-------------+ | clientID | name       | routine          | standardFee | +----------+------------+------------------+-------------+ |        1 | Gladys     | song and dance   |      196.00 | |        2 | Katherine  | standup          |      196.00 | |        3 | Georgeanna | standup          |      196.00 | |        4 | Wanda      | song and dance   |      196.00 | |        5 | Ann        | trained squirrel |      111.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

Như đã đề cập trước đây, bạn cũng có thể cập nhật dữ liệu trong nhiều cột bằng một câu lệnh UPDATE duy nhất. Để thực hiện việc này, bạn phải chỉ định mọi cột bạn muốn cập nhật, theo sau mỗi cột là biểu thức giá trị tương ứng, sau đó phân tách từng cột và cặp biểu thức giá trị bằng dấu phẩy.

Ví dụ: giả sử bạn biết rằng địa điểm nơi khách hàng của bạn biểu diễn đã báo cáo sai số lượng người tham dự cho tất cả các buổi biểu diễn của Georgeanna và Wanda. Thật tình cờ, bạn cũng tình cờ nhập sai giá vé cho mỗi buổi biểu diễn của họ.

Trước khi cập nhật dữ liệu trong bảng shows , hãy chạy truy vấn sau để truy xuất tất cả dữ liệu hiện được giữ trong đó:

  • SELECT * FROM shows; 
Output
+--------+------------+----------+------------+-------------+ | showID | showDate   | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ |      1 | 2019-12-25 |        4 |        124 |       15.00 | |      2 | 2020-01-11 |        5 |         84 |       29.50 | |      3 | 2020-01-17 |        3 |        170 |       12.99 | |      4 | 2020-01-31 |        5 |        234 |       14.99 | |      5 | 2020-02-08 |        1 |         86 |       25.00 | |      6 | 2020-02-14 |        3 |        102 |       39.50 | |      7 | 2020-02-15 |        2 |        101 |       26.50 | |      8 | 2020-02-27 |        2 |        186 |       19.99 | |      9 | 2020-03-06 |        4 |        202 |       30.00 | |     10 | 2020-03-07 |        5 |        250 |        8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.01 sec)

Để phản ánh số lượng và giá thực tế, bạn cập nhật bảng để thêm hai mươi người tham dự vào mỗi buổi biểu diễn của họ và tăng mỗi giá trị giá ticketPrice của họ lên năm mươi phần trăm. Bạn có thể làm như vậy với một thao tác như sau:

  • UPDATE shows 
  • SET attendance = attendance + 20, 
  • ticketPrice = ticketPrice * 1.5 
  • WHERE clientID IN  
  • (SELECT clientID 
  • FROM clients 
  • WHERE name = 'Georgeanna' OR name = 'Wanda'); 
Output
Query OK, 4 rows affected, 1 warning (0.00 sec) Rows matched: 4  Changed: 4  Warnings: 1

Lưu ý ví dụ này sử dụng một truy vấn con trong WHERE để trả về các giá trị clientID của Georgeanna và Wanda từ bảng clients . Thông thường, các giá trị trừu tượng như số nhận dạng có thể khó nhớ, nhưng phương pháp sử dụng truy vấn con để tìm giá trị này có thể hữu ích khi bạn chỉ biết một số thuộc tính nhất định về các bản ghi được đề cập.

Sau khi cập nhật bảng shows , hãy truy vấn nó để xác nhận các thay đổi đã diễn ra như mong đợi:

  • SELECT * FROM shows; 
Output
+--------+------------+----------+------------+-------------+ | showID | showDate   | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ |      1 | 2019-12-25 |        4 |        144 |       22.50 | |      2 | 2020-01-11 |        5 |         84 |       29.50 | |      3 | 2020-01-17 |        3 |        190 |       19.49 | |      4 | 2020-01-31 |        5 |        234 |       14.99 | |      5 | 2020-02-08 |        1 |         86 |       25.00 | |      6 | 2020-02-14 |        3 |        122 |       59.25 | |      7 | 2020-02-15 |        2 |        101 |       26.50 | |      8 | 2020-02-27 |        2 |        186 |       19.99 | |      9 | 2020-03-06 |        4 |        222 |       45.00 | |     10 | 2020-03-07 |        5 |        250 |        8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.00 sec)

, kết quả này cho biết rằng câu lệnh UPDATE đã hoàn tất thành công.

Sử dụng điều khoản JOIN để cập nhật dữ liệu trong nhiều bảng

Cho đến thời điểm này, hướng dẫn này chỉ trình bày cách cập nhật dữ liệu trong một bảng tại một thời điểm. Tuy nhiên, một số triển khai SQL cho phép bạn cập nhật nhiều cột trong nhiều bảng bằng cách tạm thời kết hợp các bảng với một mệnh đề JOIN .

Đây là cú pháp chung mà bạn có thể sử dụng để cập nhật nhiều bảng với mệnh đề JOIN :

  • UPDATE table_1 JOIN table_2 
  • ON table_1.related_column = table_2.related_column 
  • SET table_1.column_name = value_expression, 
  • table_2.column_name = value_expression 
  • WHERE conditions_apply;

Cú pháp ví dụ này bắt đầu bằng từ khóa UPDATE theo sau là tên của hai bảng, được phân tách bằng mệnh đề JOIN . Theo sau đó là mệnh đề ON , mô tả cách truy vấn nên nối hai bảng với nhau.

Trong hầu hết các triển khai, bạn có thể nối các bảng bằng cách tìm các kết quả phù hợp giữa bất kỳ tập hợp cột nào có tiêu chuẩn SQL gọi là kiểu dữ liệu " JOIN đủ điều kiện". Điều này nghĩa là , nói chung, bạn có thể kết hợp bất kỳ cột nào chứa dữ liệu số với bất kỳ cột nào khác chứa dữ liệu số, dù kiểu dữ liệu tương ứng của chúng là gì. Tương tự như vậy, bạn có thể nối bất kỳ cột nào chứa giá trị ký tự với bất kỳ cột nào khác chứa dữ liệu ký tự.

Lưu ý vì mệnh đề JOIN so sánh nội dung của nhiều hơn một bảng, nên cú pháp ví dụ này chỉ định bảng để chọn từng cột bằng cách đặt trước tên của cột với tên của bảng và dấu chấm. Đây được gọi là tham chiếu cột đủ điều kiện . Bạn có thể chỉ định bảng nào nên chọn cột như thế này cho bất kỳ thao tác nào, mặc dù nó thường chỉ được sử dụng để cải thiện độ rõ ràng khi làm việc với nhiều bảng.

Để minh họa với các bảng mẫu đã tạo trước đó, hãy chạy câu lệnh UPDATE sau. Điều này sẽ tham gia các clients và shows bảng trên tương ứng của họ clientID cột, và sau đó cập nhật routine và ticketPrice giá trị cho kỷ lục Gladys trong các clients bảng và mỗi buổi biểu diễn của bạn được liệt kê trong shows bảng:

  • UPDATE clients JOIN shows 
  • USING (clientID) 
  • SET clients.routine = 'mime', 
  • shows.ticketPrice = 30 
  • WHERE name = 'Gladys'; 
Output
Query OK, 2 rows affected (0.01 sec) Rows matched: 2  Changed: 2  Warnings: 0

Lưu ý ví dụ này kết hợp các bảng với từ khóa USING thay vì từ khóa ON được sử dụng trong cú pháp ví dụ trước. Điều này có thể thực hiện được vì cả hai bảng đều có cột clientID chia sẻ kiểu dữ liệu tương tự.

Để có hướng dẫn chuyên sâu hơn về các hoạt động JOIN , hãy xem hướng dẫn của ta về Cách sử dụng phép nối trong SQL .

Thay đổi Hành vi UPDATE Khóa Ngoại

Theo mặc định, bất kỳ câu lệnh UPDATE nào gây ra xung đột với ràng buộc FOREIGN KEY sẽ không thành công.

Nhớ lại phần Kết nối với MySQL và Cài đặt Database Mẫu của Yêu cầu rằng cột clientID của bảng shows là một foreign keys tham chiếu đến cột clientID của bảng clients . Điều này nghĩa là bất kỳ giá trị nào được nhập vào cột clientID của bảng shows phải tồn tại trong giá trị của bảng clients .

Nếu bạn cố gắng cập nhật giá trị clientID của một bản ghi trong bảng clients cũng xuất hiện trong cột clientID của bảng shows , nó sẽ gây ra lỗi:

  • UPDATE clients 
  • SET clientID = 9 
  • WHERE name = 'Ann'; 
Output
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Bạn có thể tránh lỗi này bằng cách thay thế ràng buộc foreign keys hiện có bằng một ràng buộc xử lý các hoạt động UPDATE cách khác.

Lưu ý : Không phải mọi hệ thống hoặc công cụ quản lý database quan hệ đều cho phép bạn thêm hoặc xóa một ràng buộc khỏi một bảng hiện có như được nêu trong các đoạn sau. Nếu bạn đang sử dụng RDBMS không phải MySQL, bạn nên tham khảo tài liệu chính thức của nó để hiểu nó có những hạn chế nào đối với việc quản lý các ràng buộc.

Để thay thế ràng buộc hiện tại, trước tiên bạn phải loại bỏ nó bằng một ALTER TABLE . Nhớ lại rằng trong câu CREATE TABLE cho các shows , ta đã định nghĩa client_fk làm tên cho ràng buộc FOREIGN KEY của bảng:

  • ALTER TABLE shows 
  • DROP FOREIGN KEY client_fk; 
Output
Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0

Sau đó, tạo một ràng buộc foreign keys mới được cấu hình để xử lý các hoạt động UPDATE theo cách phù hợp với trường hợp sử dụng nhất định. Ngoài cài đặt mặc định cấm các câu lệnh UPDATE vi phạm foreign keys , có hai tùy chọn khác có sẵn trên hầu hết các RDBMS:

  • ON UPDATE SET NULL : Tùy chọn này sẽ cho phép bạn cập nhật các bản ghi từ bảng mẹ và sẽ đặt lại bất kỳ giá trị nào trong bảng con tham chiếu chúng là NULL .
  • ON UPDATE CASCADE : Khi bạn cập nhật một hàng trong bảng mẹ, tùy chọn này sẽ khiến SQL tự động cập nhật bất kỳ bản ghi nào tham chiếu đến nó trong bảng con để chúng căn chỉnh với giá trị mới trong bảng mẹ.

Đối với mục đích của ví dụ này, ON UPDATE SET NULL không có ý nghĩa. Sau cùng, nếu bạn thay đổi một trong các số nhận dạng khách hàng của bạn nhưng không xóa chúng khỏi bảng clients , chúng vẫn sẽ được liên kết với màn trình diễn của họ trong bảng shows . Số nhận dạng mới của họ phải được phản ánh trong profile biểu diễn của họ, vì vậy tùy chọn ON UPDATE CASCADE có ý nghĩa hơn đối với mục đích của ta .

Để thêm ràng buộc FOREIGN KEY tuân theo hành vi ON UPDATE CASCADE , hãy chạy ALTER TABLE sau. Điều này tạo ra một ràng buộc mới có tên new_client_fk sao chép định nghĩa ràng buộc trước đó, nhưng bao gồm tùy chọn ON UPDATE CASCADE :

  • ALTER TABLE shows 
  • ADD CONSTRAINT new_client_fk 
  • FOREIGN KEY (clientID) 
  • REFERENCES clients (clientID) 
  • ON UPDATE CASCADE; 
Output
Query OK, 10 rows affected (0.02 sec) Records: 10  Duplicates: 0  Warnings: 0

Kết quả này cho bạn biết rằng thao tác đã ảnh hưởng đến tất cả mười hàng trong bảng shows .

Lưu ý : Thay vì thay đổi định nghĩa của bảng để thay đổi cách foreign keys xử lý các thao tác UPDATE , bạn có thể xác định hành vi này ngay từ đầu trong câu CREATE TABLE như sau:

  • CREATE TABLE shows 
  • (showID int PRIMARY KEY, 
  • showDate date, 
  • clientID int, 
  • attendance int, 
  • ticketPrice decimal (4,2), 
  • CONSTRAINT client_fk 
  • FOREIGN KEY (clientID) 
  • REFERENCES clients(clientID) 
  • ON UPDATE CASCADE 
  • );

Sau đó, bạn có thể cập nhật giá trị clientID của bất kỳ bản ghi nào trong bảng clients và những thay đổi đó sẽ chuyển xuống bất kỳ hàng nào trong bảng shows tham chiếu đến nó:

  • UPDATE clients 
  • SET clientID = 9 
  • WHERE name = 'Ann'; 
Output
Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0

Mặc dù kết quả này cho biết nó chỉ ảnh hưởng đến một hàng, nhưng nó cũng sẽ cập nhật giá trị clientID của bất kỳ bản ghi hiệu suất nào trong bảng shows liên kết với Ann. Để xác nhận điều này, hãy chạy truy vấn sau để truy xuất tất cả dữ liệu từ bảng shows :

  • SELECT * FROM shows; 
Output
+--------+------------+----------+------------+-------------+ | showID | showDate   | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ |      1 | 2019-12-25 |        4 |        144 |       22.50 | |      2 | 2020-01-11 |        9 |         84 |       29.50 | |      3 | 2020-01-17 |        3 |        190 |       19.49 | |      4 | 2020-01-31 |        9 |        234 |       14.99 | |      5 | 2020-02-08 |        1 |         86 |       30.00 | |      6 | 2020-02-14 |        3 |        122 |       59.25 | |      7 | 2020-02-15 |        2 |        101 |       26.50 | |      8 | 2020-02-27 |        2 |        186 |       19.99 | |      9 | 2020-03-06 |        4 |        222 |       45.00 | |     10 | 2020-03-07 |        9 |        250 |        8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.00 sec)

Như mong đợi, bản cập nhật được thực hiện cho cột clientID trong bảng clients xuống các hàng được liên kết trong bảng shows .

Kết luận

Bằng cách đọc hướng dẫn này, bạn đã học cách thay đổi các bản ghi hiện có trong hoặc nhiều bảng bằng cách sử dụng câu lệnh UPDATE của SQL. Bạn cũng đã học cách SQL xử lý các thao tác UPDATE xung đột với các ràng buộc foreign keys và cách thay đổi hành vi mặc định đó.

Các lệnh được nêu ở đây sẽ hoạt động trên bất kỳ hệ quản trị database nào sử dụng SQL. Lưu ý mọi database SQL sử dụng cách triển khai ngôn ngữ duy nhất của riêng nó, vì vậy bạn nên tham khảo tài liệu chính thức của DBMS để có mô tả đầy đủ hơn về cách nó xử lý các hoạt động UPDATE và những tùy chọn nào có sẵn cho chúng.

Nếu bạn muốn tìm hiểu thêm về cách làm việc với SQL, ta khuyến khích bạn xem các hướng dẫn khác trong loạt bài này về Cách sử dụng SQL .

Tags:

Các tin liên quan