Cột Identity trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng thuộc tính IDENTITY trong SQL Server để thêm cột định danh vào bảng.

Giới thiệu về cột IDENTITY trong SQL Server

Để tạo cột định danh cho bảng, bạn sử dụng thuộc tính IDENTITY như sau:

IDENTITY[(seed,increment)]

Trong cú pháp này:

• seed là giá trị của hàng đầu tiên insert vào bảng.
• increment là giá trị gia tăng được thêm vào giá trị định danh của hàng trước đó.

Giá trị mặc định của seed và increment là (1,1). Có nghĩa là hàng đầu tiên được tải vào bảng sẽ có giá trị là một, hàng thứ hai sẽ có giá trị là 2, tiếp theo là 3, 4, v.v.

Giả sử, bạn muốn giá trị của cột nhận dạng của hàng đầu tiên là 10 và giá trị gia tăng là 10, bạn sử dụng cú pháp sau:

IDENTITY (10,10)

Lưu ý: SQL Server chỉ cho phép bạn có một cột nhận dạng trên mỗi bảng.

Ví dụ về cột IDENTITY trong SQL Server

Hãy tạo một lược đồ mới có tên là hr để thực hành:

CREATE SCHEMA hr;

Câu lệnh sau tạo một bảng mới bằng cách sử dụng thuộc tính IDENTITY cho cột person_id:

CREATE TABLE hr.person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);

Đầu tiên, hãy insert một hàng mới vào bảng person:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('John','Doe', 'M');

Kết quả trả về như sau:

Như có thể thấy rõ ràng từ đầu ra, hàng đầu tiên đã được insert thành công và trả về giá trị của cột person_id là 1.

Tiếp theo, insert một hàng khác vào bảng person:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jane','Doe','F');

Kết quả như sau:

Như bạn có thể thấy rõ từ đầu ra, hàng thứ hai đã được insert thành công và trả về giá trị của cột person_id là 2.

Tái sử dụng các giá trị định danh

SQL Server không sử dụng lại các giá trị định danh. Nếu bạn insert một hàng vào cột định danh và câu lệnh insert không thành công hoặc bị rollback, thì giá trị nhận dạng sẽ bị mất và sẽ không được tạo lại. Điều này dẫn đến khoảng trống trong cột danh tính.

Hãy xem xét ví dụ sau.

Đầu tiên, hãy tạo thêm hai bảng trong lược đồ hr có tên là position và person_position:

CREATE TABLE hr. POSITION (
	position_id INT IDENTITY (1, 1) PRIMARY KEY,
	position_name VARCHAR (255) NOT NULL,

);

CREATE TABLE hr.person_position (
	person_id INT,
	position_id INT,
	PRIMARY KEY (person_id, position_id),
	FOREIGN KEY (person_id) REFERENCES hr.person (person_id),
	FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
);

Tiếp theo, insert một người mới và chỉ định vị trí cho người mới này bằng cách insert một hàng mới vào bảng person_position:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new person
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Joan','Smith','F');

        -- assign the person a position
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH

    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;
GO

Trong ví dụ này, câu lệnh insert đầu tiên đã được thực thi thành công. Tuy nhiên, câu lệnh thứ hai không thành công do không có id bằng 1 trong bảng position. Do xảy ra lỗi, toàn bộ giao dịch đã bị rollback.

Bởi vì câu lệnh INSERT đầu tiên sử dụng giá trị định danh là 3 và giao dịch đã được khôi phục lại, giá trị nhận dạng tiếp theo sẽ là 4 như được hiển thị trong câu lệnh sau:

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');

Đầu ra của câu lệnh là:

Trong hướng dẫn này, bạn đã học cách sử dụng thuộc tính IDENTITY trong SQL Server để tạo cột định danh cho bảng.