SCHEMA trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách tạo, chỉnh sửa và xóa lược đồ (schema) trong cơ sở dữ liệu SQL Server.

Schema trong SQL Server là gì

Lược đồ (schema) là tập hợp các đối tượng cơ sở dữ liệu bao gồm table, view, trigger, stored procedure, index, v.v. Một lược đồ được liên kết với username được gọi là chủ sở hữu lược đồ, chủ sở hữu của các đối tượng cơ sở dữ liệu liên quan về mặt logic.

Một lược đồ luôn thuộc về một cơ sở dữ liệu. Mặt khác, một cơ sở dữ liệu có thể có một hoặc nhiều lược đồ. Ví dụ, trong cơ sở dữ liệu mẫu BikeStores, chúng tôi có hai lược đồ: sales và production.

Một đối tượng đủ điều kiện trong lược đồ bằng cách sử dụng định dạng schema_name.object_name, ví dụ: sales.orders. Hai bảng trong hai lược đồ có thể dùng chung tên nên bạn có thể có hr.employees và sales.employees.

Lược đồ tích hợp trong SQL Server

SQL Server cung cấp cho chúng ta một số lược đồ được định nghĩa trước, ví dụ: dbo, guest, sys, và INFORMATION_SCHEMA.

Lưu ý rằng SQL Server sử dụng các lược đồ sys và INFORMATION_SCHEMA cho các đối tượng hệ thống, do đó, bạn không thể tạo hoặc xóa bất kỳ đối tượng nào trong các lược đồ này.

Lược đồ mặc định cho cơ sở dữ liệu mới được tạo là dbo, thuộc sở hữu của tài khoản người dùng dbo. Theo mặc định, khi bạn tạo người dùng mới bằng lệnh CREATE USER, người dùng sẽ lấy dbo làm lược đồ mặc định của nó.

Tạo lược đồ bằng câu lệnh CREATE SCHEMA trong SQL Server

Tổng quan về câu lệnh CREATE SCHEMA trong SQL Server

Câu lệnh CREATE SCHEMA cho phép bạn tạo một lược đồ mới trong cơ sở dữ liệu hiện tại.

Phần sau minh họa phiên bản đơn giản hóa của câu lệnh CREATE SCHEMA:

CREATE SCHEMA schema_name
    [AUTHORIZATION owner_name]

Trong cú pháp này,

• Đầu tiên, chỉ định tên của lược đồ mà bạn muốn tạo trong mệnh đề CREATE SCHEMA.
• Tiếp theo, chỉ định chủ sở hữu của lược đồ sau từ khóa AUTHORIZATION.

Ví dụ về câu lệnh CREATE SCHEMA trong SQL Server

Ví dụ sau cho thấy cách sử dụng câu lệnh CREATE SCHEMA để tạo lược đồ customer_services:

CREATE SCHEMA customer_services;
GO

Lưu ý rằng lệnh GO hướng dẫn SQL Server Management Studio gửi các câu lệnh SQL ở phía trên lệnh GO tới máy chủ để thực thi.

Sau khi thực hiện câu lệnh, bạn có thể tìm thấy lược đồ mới được tạo trong Security > Schemas như hình dưới đây.

Nếu bạn muốn liệt kê tất cả các lược đồ trong cơ sở dữ liệu hiện tại, bạn có thể truy vấn các lược đồ từ sys.schemas như được minh họa trong truy vấn sau:

SELECT 
    s.name AS schema_name, 
    u.name AS schema_owner
FROM 
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY 
    s.name;

Đây là kết quả:

Sau khi có lược đồ customer_services, bạn có thể tạo các đối tượng cho lược đồ. Ví dụ: câu lệnh sau tạo một bảng mới có tên jobs trong lược đồ customer_services:

CREATE TABLE customer_services.jobs(
    job_id INT PRIMARY KEY IDENTITY,
    customer_id INT NOT NULL,
    description VARCHAR(200),
    created_at DATETIME2 NOT NULL
);

Chỉnh sửa lược đồ bằng câu lệnh ALTER SCHEMA trong SQL Server

Tổng quan về câu lệnh ALTER SCHEMA trong SQL Server

Câu lệnh ALTER SCHEMA cho phép bạn chuyển các securable từ một lược đồ sang một lược đồ khác trong cùng một cơ sở dữ liệu.

Lưu ý rằng: securable là tài nguyên mà hệ thống ủy quyền của Database Engine kiểm soát quyền truy cập. Ví dụ, một bảng là một securable.

Sau đây là cú pháp của câu lệnh ALTER SCHEMA:

ALTER SCHEMA target_schema_name   
    TRANSFER [ entity_type :: ] securable_name;

Trong cú pháp này:

• target_schema_name là tên của một lược đồ trong cơ sở dữ liệu hiện tại mà bạn muốn di chuyển đối tượng vào đó. Lưu ý rằng nó không thể là SYS hoặc INFORMATION_SCHEMA.
• entity_type có thể là Đối tượng, Loại hoặc Tập hợp lược đồ XML. Nó mặc định là Đối tượng. entity_type đại diện cho lớp của thực thể mà chủ sở hữu đang được thay đổi.
• securable_name là tên của securable mà bạn muốn chuyển vào target_schema_name.

Nếu bạn di chuyển một stored procedure, function, view hoặc trigger, SQL Server sẽ không thay đổi tên lược đồ của các securable này. Do đó, bạn nên xóa và tạo lại các đối tượng này trong lược đồ mới thay vì sử dụng câu lệnh ALTER SCHEMA để di chuyển.

Nếu bạn di chuyển một đối tượng, ví dụ: table hoặc synonym, SQL Server sẽ không tự động cập nhật các tham chiếu cho các đối tượng này. Bạn phải sửa đổi thủ công các tham chiếu để phản ánh tên lược đồ mới. Ví dụ: nếu bạn di chuyển một table được tham chiếu trong một stored procedure, bạn phải sửa đổi stored procedure để phản ánh tên lược đồ mới.

Ví dụ về câu lệnh ALTER SCHEMA trong SQL Server

Đầu tiên, hãy tạo một bảng mới có tên offices trong lược đồ dbo:

CREATE TABLE dbo.offices
(
    office_id      INT
    PRIMARY KEY IDENTITY, 
    office_name    NVARCHAR(40) NOT NULL, 
    office_address NVARCHAR(255) NOT NULL, 
    phone          VARCHAR(20),
);

Tiếp theo, insert một số hàng vào bảng dbo.offices:

INSERT INTO dbo.offices(office_name, office_address)
VALUES ('Silicon Valley','400 North 1st Street, San Jose, CA 95130'),
    ('Sacramento','1070 River Dr., Sacramento, CA 95820');

Sau đó, tạo một stored procedure để tìm văn phòng theo id văn phòng:

CREATE PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT 
        * 
    FROM 
        dbo.offices
    WHERE 
        office_id = @id;
END;

Cuối cùng, chuyển bảng dbo.offices này sang lược đồ sales:

ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices;  

Nếu bạn thực thi stored procedure usp_get_office_by_id, SQL Server sẽ xuất hiện lỗi:

Msg 208, Level 16, State 1, Procedure usp_get_office_by_id, Line 5 [Batch Start Line 30]
Invalid object name 'dbo.offices'.

Để khắc phục lỗi này, hãy sửa đổi stored procedure sử dụng lược đồ mới:

ALTER PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT 
        * 
    FROM 
        sales.offices
    WHERE 
        office_id = @id;
END;

Xóa schema bằng câu lệnh DROP SCHEMA trong SQL Server

Tổng quan về câu lệnh DROP SCHEMA trong SQL Server

Câu lệnh DROP SCHEMA cho phép bạn xóa một lược đồ khỏi cơ sở dữ liệu. Sau đây là cú pháp của câu lệnh DROP SCHEMA:

DROP SCHEMA [IF EXISTS] schema_name;

Trong cú pháp này:

• Đầu tiên, chỉ định tên của lược đồ mà bạn muốn xóa. Nếu lược đồ chứa bất kỳ đối tượng nào, câu lệnh sẽ không thành công. Do đó, bạn phải xóa tất cả các đối tượng trong lược đồ trước khi xóa lược đồ.
• Tiếp theo, sử dụng tùy chọn IF EXISTS để xóa lược đồ chỉ khi lược đồ tồn tại. Cố gắng xóa một lược đồ không tồn tại mà không có tùy chọn IF EXISTS sẽ dẫn đến lỗi.

Ví dụ về câu lệnh DROP SCHEMA trong SQL Server

Đầu tiên, hãy tạo một lược đồ mới có tên là logistics:

CREATE SCHEMA logistics;
GO

Tiếp theo, tạo một bảng mới có tên deliveries bên trong lược đồ logistics:

CREATE TABLE logistics.deliveries
(
    order_id        INT
    PRIMARY KEY, 
    delivery_date   DATE NOT NULL, 
    delivery_status TINYINT NOT NULL
);

Sau đó, xóa lược đồ logistics:

DROP SCHEMA logistics;

SQL Server sẽ thông báo lỗi sau vì lược đồ không trống.

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'logistics' because it is being referenced by object 'deliveries'.

Để xóa lược đồ này, trước tiên bạn phải xóa bảng logistics.deliveries bằng lệnh sau:

DROP TABLE logistics.deliveries;

Cuối cùng, sử dụng lệnh DROP SCHEMA một lần nữa để xóa lược đồ logistics:

DROP SCHEMA IF EXISTS logistics;

Bây giờ, bạn sẽ thấy rằng lược đồ logistics đã bị xóa khỏi cơ sở dữ liệu.

Trong hướng dẫn này, bạn đã học cách tạo, chỉnh sửa và xóa lược đồ (schema) trong cơ sở dữ liệu SQL Server.