Tạo nhiều bảng trong PostgreSQL - phần 1¶

Khái quát cơ sở dữ liệu¶

Cơ sở dữ liệu cần tạo là school_db, dùng để quản lý điểm số của học sinh, gồm một số bảng như sau:

Bài trước đã hướng dẫn cách tạo một bảng, là students.

Bài này trình bày cách tạo thêm bảng classrooms và kết nối với bảng students.

Mở tập tin sql¶

1. Trong cửa sổ Query Tool của pgAdmin 4, nhấn nút Open File.

2. Chọn tập tin school_db.sql đã lưu trong bài trước.

Bảng classrooms¶

Tạo bảng¶

Mô tả của bảng classrooms như sau:

Để tạo bảng classrooms ứng với mô tả trên, ta thực hiện như sau:

1. Trong tab Query, nhập tiếp câu lệnh sau để tạo bảng classrooms:

36
37
38
39
40
41
42

-- Tạo bảng classrooms
create table classrooms (
    classroom_id char(5) primary key,
    classroom_name varchar(50),
    room varchar(50),
    head_teacher varchar(50)
);

2. Quét khối câu lệnh SQL vừa viết, rồi nhấn F5 để chạy. (Hoặc chỉ cần đặt con trỏ vào vị trí bất kỳ trong câu lệnh, rồi nhấn Alt + F5).

Thêm dữ liệu¶

Mã lệnh SQL thêm mới ba mẫu tin (1) vào bảng classrooms:

1. Mỗi mẫu tin là một hàng trong bảng, chứa dữ liệu của một thực thể hoặc một sự kiện, cụ thể ở đây, mỗi mẫu tin là dữ liệu của một lớp.

44
45
46
47
48

-- Thêm dữ liệu vào bảng classrooms
insert into classrooms -- (1)!
values ('12CTo', '12 chuyên Toán', '3.14', 'Prof. Ngô Bảo Châu'),
        ('11CTi', '11 chuyên Tin', 'Fibonacci', 'Mr School'),
        ('10CSi', '10 chuyên Sinh', 'Darwin', 'Dr Black Jack');

1. Do thêm dữ liệu vào tất cả thuộc tính trong bảng, ta không cần liệt kê tên từng thuộc tính.

Kết nối hai bảng¶

Ta cần kết nối hai bảng students và classrooms với nhau nhằm lưu trữ và quản lý thông tin "một học sinh học ở lớp nào" hoặc "một lớp có những học sinh nào". (1)

1. Kết nối còn được gọi là tạo mối quan hệ giữa hai bảng.

Để kết nối hai bảng, ta cần thực hiện như sau:

1. Xác định một thuộc tính chung giữa hai bảng. Cụ thể ở đây là thuộc tính classroom_id.

2. Tạo tham chiếu từ bảng này đến bảng kia. Cụ thể, tạo tham chiếu từ thuộc tính classroom_id của bảng students đến thuộc tính classroom_id của bảng classrooms.

Hiện nay, bảng students chưa có thuộc tính classroom_id. Cho nên, ta cần thêm thuộc tính classroom_id trước.

Thêm thuộc tính cho bảng¶

Để thêm thuộc tính (cột) cho bảng, ta dùng câu lệnh SQL ALTER TABLE.

ALTER TABLE bảng
ADD COLUMN thuộc_tính_mới kiểu_dữ_liệu;

Ví dụ:

Mã lệnh SQL thêm thuộc tính classroom_id vào bảng students:

50
51
52

-- Thêm thuộc tính classroom_id vào bảng students
alter table students
add column classroom_id char(5);

Kết nối bảng¶

Để kết nối (nói cách khác là tạo mối quan hệ) giữa hai bảng, ta cần dựa vào khoá ngoại (1) và khoá chính của chúng.

1. Khóa ngoại là một hoặc nhiều thuộc tính (cột) tham chiếu đến khoá chính của bảng khác, nhằm bảo đảm tính toàn vẹn dữ liệu và duy trì mối quan hệ giữa các bảng.

Theo đó, ta cần tạo ra ràng buộc khoá ngoại cho thuộc tính classroom_id trong bảng students. Ràng buộc này bảo đảm rằng giá trị của khoá ngoại classroom_id trong bảng students phải tham chiếu đến khoá chính classroom_id của bảng classrooms.

Nói cách khác, giá trị classroom_id của mỗi học sinh trong bảng students phải khớp với một giá trị classroom_id có sẵn trong bảng classrooms.

Chẳng hạn, nếu ta muốn cập nhật classroom_id của một học sinh thành 10A17, mà giá trị 10A17 lại không tồn tại trong bảng classrooms, thì cơ sở dữ liệu sẽ không cho cập nhật. Ràng buộc khoá ngoại ngăn chặn cập nhật là nhằm bảo đảm tính nhất quán của cơ sở dữ liệu.

Để bổ sung ràng buộc khoá ngoại cho một bảng, ta cũng dùng câu lệnh SQL ALTER TABLE.

ALTER TABLE bảng
ADD CONSTRAINT tên_của_ràng_buộc FOREIGN KEY (thuộc_tính_muốn_tham_chiếu)
REFERENCES bảng_được_tham_chiếu(thuộc_tính_được_tham_chiếu);

Ví dụ:

Mã lệnh SQL thêm ràng buộc khoá ngoại từ bảng students đến bảng classrooms:

54
55
56
57

-- Thêm tham chiếu từ students đến classrooms
alter table students
add constraint fk_classroom_id foreign key (classroom_id) -- (1)!
references classrooms(classroom_id); -- (2)!

1. fk_classroom_id là tên của ràng buộc.

   classroom_id là thuộc tính sẽ làm khoá ngoại của của bảng students.

2. Dòng này có nghĩa là: tham chiếu đến classroom_id của bảng classrooms.

Sau câu lệnh trên, mối quan hệ giữa hai bảng students và classrooms được thể hiện qua sơ đồ dưới đây:

Trong đó:

• Bảng classrooms được gọi là bảng cha.
• Bảng students được gọi là bảng con.

Cập nhật giá trị của thuộc tính¶

Để cập nhật giá trị của thuộc tính trong bảng, ta dùng câu lệnh SQL UPDATE.

UPDATE bảng
SET thuộc_tính_1 = giá_trị_1, thuộc_tính_2 = giá_trị_2, ...
WHERE điều_kiện_của_các_mẫu_tin_sẽ_cập_nhật;

Ví dụ:

Mã lệnh SQL cập nhật giá trị của thuộc tính classroom_id trong bảng students:

58
59
60
61
62
63
64
65
66
67
68
69

-- Cập nhật dữ liệu lớp cho bảng students
update students
set classroom_id = '12CTo'
where student_id in ('221001', '220002', '220003', '220091', '221017'); -- (1)!

update students
set classroom_id = '11CTi'
where student_id in ('231001', '231002', '231003', '230004', '231005', '230006', '231007', '230008', '230009');

update students
set classroom_id = '10CSi'
where student_id in ('241001', '240010', '240011', '240012');

1. where: dùng để lọc ra các mẫu tin thoả điều kiện.

   student_id in ( ... ): các mẫu tin có giá trị student_id bằng một trong các giá trị trong danh sách sẽ được cập nhật.

Xem toàn bộ dữ liệu của bảng students vừa cập nhật bằng cách: click phải lên bảng students > chọn View/Edit Data > chọn All Rows.

Sơ đồ tóm tắt nội dung¶