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.