Các lệnh thiết kế cấu trúc trong PostgreSQL

Các lệnh thiết kế cấu trúc trong PostgreSQL là nhóm lệnh quan trọng giúp xây dựng và quản lý cấu trúc của cơ sở dữ liệu. Trong quá trình phát triển hệ thống, việc thiết kế cấu trúc bảng, định nghĩa các cột, thiết lập ràng buộc và tạo mối quan hệ giữa các bảng là bước nền tảng trước khi dữ liệu được đưa vào hệ thống.

Trong SQL, các thao tác liên quan đến cấu trúc cơ sở dữ liệu được thực hiện thông qua nhóm lệnh DDL (Data Definition Language). Nhóm lệnh này cho phép chúng ta tạo mới, thay đổi hoặc xóa các đối tượng trong cơ sở dữ liệu như bảng, chỉ mục hoặc view.

Các lệnh DDL phổ biến nhất gồm:

• CREATE – tạo đối tượng mới (table, index, view…)
• ALTER – thay đổi cấu trúc của đối tượng đã tồn tại
• DROP – xóa đối tượng khỏi cơ sở dữ liệu

Việc hiểu và sử dụng đúng các lệnh này giúp thiết kế cơ sở dữ liệu chặt chẽ, đảm bảo tính toàn vẹn dữ liệu và dễ dàng mở rộng trong các hệ thống thực tế.

1. CREATE TABLE – Tạo bảng

Trong cơ sở dữ liệu quan hệ, bảng (table) là nơi lưu trữ dữ liệu. Mỗi bảng gồm nhiều cột (column) với các kiểu dữ liệu khác nhau.

Trước khi có thể thêm dữ liệu vào hệ thống, chúng ta cần tạo bảng trước.

Cú pháp tổng quát như sau:

CREATE TABLE ten_bang (
cot1 datatype [constraint],
cot2 datatype [constraint],
...
[table_constraints]
);

Trong đó:

• cot1, cot2 : là tên các cột trong bảng
• datatype: kiểu dữ liệu của cột
• constraint: ràng buộc dữ liệu trên cột
• table_constraints: các ràng buộc áp dụng cho toàn bộ bảng

Mời xem ví dụ: Tạo bảng customers đơn giản

CREATE TABLE customers (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(15)
);

Ví dụ trên chỉ minh họa cú pháp cơ bản, chưa tối ưu các ràng buộc dữ liệu.

Ví dụ: Tạo bảng products với các ràng buộc

CREATE TABLE products (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- Khóa chính, tự động tăng
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    brand VARCHAR(200),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trong thiết kế này:

• GENERATED ALWAYS AS IDENTITY PRIMARY KEY : PostgreSQL tự động tạo ID tăng dần cho mỗi dòng.
• NOT NULL : Cột bắt buộc phải có dữ liệu.
• DEFAULT : Thiết lập giá trị mặc định cho cột.
• CHECK : Ràng buộc điều kiện hợp lệ cho dữ liệu.

2. DROP TABLE – Xóa bảng

Khi cần xóa hoàn toàn một bảng khỏi cơ sở dữ liệu, chúng ta sử dụng lệnh DROP TABLE. Cú pháp như sau :

DROP TABLE ten_bang;

Ví dụ: Xóa bảng customers vừa tạo

DROP TABLE customers;

Lệnh này xóa toàn bộ bảng và tất cả dữ liệu bên trong.

Để tránh lỗi khi bảng không tồn tại, PostgreSQL cho phép sử dụng:

DROP TABLE IF EXISTS customers;

Ví dụ: Tạo lại bảng customers với thiết kế tốt hơn

CREATE TABLE customers (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(254) UNIQUE NOT NULL,
    phone VARCHAR(15) NOT NULL,
    address TEXT NOT NULL,
    joined_at DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Một số điểm quan trọng trong thiết kế này:

• PRIMARY KEY đảm bảo mỗi bản ghi là duy nhất
• GENERATED ALWAYS AS IDENTITY PRIMARY KEY:  PostgreSQL tự tăng giá trị ID.
• NOT NULL: bắt buộc phải có dữ liệu.
• UNIQUE:  không được trùng giá trị trong cột.

3. ALTER TABLE – Chỉnh sửa cấu trúc của bảng

Trong thực tế, cấu trúc bảng hiếm khi hoàn hảo ngay từ đầu. Khi hệ thống phát triển, chúng ta có thể cần:

• thêm cột mới
• xóa cột
• đổi tên cột
• thay đổi kiểu dữ liệu
• thêm ràng buộc

Lệnh ALTER TABLE cho phép thực hiện các thay đổi này.

3.1 Thêm cột mới

Câu lệnh để thêm 1 cột mới vào trong table như sau:

ALTER TABLE ten_bang
ADD COLUMN ten_cot kieu_du_lieu;

Ví dụ: thêm ngày sinh và trạng thái hoạt động cho khách hàng

ALTER TABLE customers
ADD COLUMN dob DATE;

ALTER TABLE customers
ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

Lưu ý: khi thực thi nhiều câu lệnh sql, dấu ;  dùng để đánh dấu kết thúc 1 lệnh.

3.2 Xóa cột

Lệnh để xóa 1 cột trong table như sau:

ALTER TABLE ten_bang
DROP COLUMN ten_cot;

Trong đó:  ten_cot là tên của cột mà bạn muốn xóa

Ví dụ: Xóa cột dob (ngày sinh)

ALTER TABLE customers
DROP COLUMN dob;

Lưu ý: nếu cột bị xóa là PRIMARY KEY thì PostgreSQL sẽ xóa luôn ràng buộc khóa và index liên quan.

3.3 Đổi tên cột

Để đổi tên một cột trong bảng, dùng Alter table và Rename column như sau:

ALTER TABLE ten_bang
RENAME COLUMN ten_cu TO ten_moi;

Trong đó:

• ten_cu: tên cột hiện tại
• ten_moi: tên cột muốn đổi thành

Ví dụ: Đổi tên cột joined_at thành ngay_gia_nhap

ALTER TABLE customers
RENAME COLUMN joined_at TO ngay_gia_nhap;

3.4 Thay đổi kiểu dữ liệu cột

ALTER TABLE ten_bang
ALTER COLUMN ten_cot TYPE kieu_du_lieu_moi;

Trong đó:

• ten_cot: tên cột muốn đổi kiểu dữ liệu
• kieu_du_lieu_moi: kiểu dữ liệu muốn chuyển thành

Ví dụ: Đổi kiểu cột phone thành VARCHAR(20)

ALTER TABLE customers
ALTER COLUMN phone TYPE VARCHAR(20);

Một số trường hợp cần lưu ý khi đổi kiểu dữ liệu:

• TEXT => INTEGER: PostgreSQL chỉ chuyển đổi được nếu toàn bộ dữ liệu là số hợp lệ.
• INTEGER => TEXT: luôn chuyển đổi được vì số có thể biểu diễn dưới dạng chuỗi.
• TIMESTAMP => DATE: phần thông tin về thời gian (giờ, phút, giây) sẽ bị loại bỏ.

3.5 Thêm ràng buộc CHECK

Ràng buộc CHECK cho phép kiểm tra điều kiện của dữ liệu trước khi lưu vào bảng. Cú pháp như sau:

ALTER TABLE ten_bang
ADD CONSTRAINT ten_rang_buoc
CHECK (dieu_kien);

Trong đó:

• ten_rang_buoc: đặt tên cho ràng buộc (tùy ý)
• CHECK: từ khóa bắt đầu biểu thức điều kiện ràng buộc

Ví dụ 1: Kiểm tra định dạng email

ALTER TABLE customers
ADD CONSTRAINT chk_email_format
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Chèn thử dữ liệu phạm ràng buộc email

INSERT INTO customers (first_name, last_name, address, phone, email)
VALUES (‘Nguyễn’, ‘Bình’,’TPHCM’, ‘0912345’,  ‘binh@google@a.com’);

Ví dụ 2: Kiểm tra định dạng số điện thoại

ALTER TABLE customers
ADD CONSTRAINT chk_phone_format
CHECK (phone ~ '^[0-9+\-\s()]+$' AND LENGTH(phone) >= 10);

Chèn thử dữ liệu vi phạm ràng buộc số điện thoại (phone)

INSERT INTO customers (first_name, last_name, address, phone, email)
VALUES (‘Nguyễn’, ‘Bình’,’TPHCM’, ‘09123’,  ‘binh@google.com’);

Ví dụ : Kiểm tra độ dài first_name và last_name chứa ít nhất 2 kí tự

ALTER TABLE customers
ADD CONSTRAINT chk_name_length
CHECK ( LENGTH(first_name) >= 2 AND LENGTH(last_name) >= 2 );

Chèn thử dữ liệu vi phạm ràng buộc độ dài first_name

INSERT INTO customers (first_name, last_name, address, phone, email)
VALUES (‘N’, ‘Bình’,’TPHCM’, ‘09123456789’,  ‘binh@google.com’);

3.6 Tạo ràng buộc khóa ngoại (FOREIGN KEY)

Khóa ngoại giúp liên kết dữ liệu giữa các bảng, đảm bảo rằng dữ liệu tham chiếu luôn hợp lệ. Cú pháp:

CONSTRAINT ten_khoa_ngoai
FOREIGN KEY (ten_cot_kn)
REFERENCES ten_bang_tc(ten_cot_tc)

Trong đó:

• ten_khoa_ngoai: tên của ràng buộc khóa ngoại (fk_orderdetail_order)
• ten_cot_kn: tên của cột khóa ngoại trên bảng hiện tại
• ten_bang_tc: tên của bảng tham chiếu
• ten_cot_tc: tên của cột tham chiếu trong bảng tham chiếu mà khóa ngoại tham chiếu tới

Ví dụ: Tạo bảng orders và ràng buộc khóa ngoại với bảng customers như sau

CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Pending'
        CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Canceled')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 

    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

Ví dụ 2:  Tạo bảng order_details và ràng buộc khóa ngoại với bảng orders và products như sau:

CREATE TABLE order_details (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_order_details
        PRIMARY KEY (order_id, product_id),

    CONSTRAINT fk_orderdetail_order
        FOREIGN KEY (order_id)
        REFERENCES orders(id),

    CONSTRAINT fk_orderdetail_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
);

Khóa ngoại giúp đảm bảo rằng giá trị trong bảng hiện tại phải tồn tại trong bảng tham chiếu.

KẾT LUẬN

Các lệnh thiết kế cấu trúc trong PostgreSQL đóng vai trò quan trọng trong việc xây dựng và quản lý hệ thống cơ sở dữ liệu.

Thông qua các lệnh như CREATE TABLE, ALTER TABLE và DROP TABLE, chúng ta có thể thiết kế bảng dữ liệu, thiết lập các ràng buộc và xây dựng mối quan hệ giữa các bảng một cách rõ ràng và chặt chẽ.

Việc nắm vững các lệnh thiết kế cấu trúc trong PostgreSQL giúp đảm bảo tính toàn vẹn dữ liệu, tăng khả năng mở rộng của hệ thống và hỗ trợ quá trình phát triển các ứng dụng dữ liệu một cách hiệu quả.

Trong bài viết tiếp theo, chúng ta sẽ tìm hiểu các lệnh thao tác dữ liệu (DML) như INSERT, UPDATE và DELETE, cho phép thêm, chỉnh sửa và xóa dữ liệu trong các bảng đã được thiết kế.