Thêm thuộc tính Cột IDENTITY trong SQL Server

Thêm thuộc tính Cột IDENTITY trong SQL Server​

1. Cột IDENTITY

• Dùng để tạo một dãy số liên tục tăng cho mỗi bản ghi được INSERT, nó rất thích hợp khi bạn cần có một cột mà mỗi lần INSERT giá trị của nó được tự động tăng lên cao hơn các giá trị trước đó, trong khi bạn không quan tâm nhiều lắm đến bản thân các giá trị trong cột này. Trong rất nhiều trường hợp, cột IDENTITY cũng được dùng luôn làm khóa chính cho bảng.

Bạn khai báo thuộc tính IDENTITY cho cột như sau:

• Tạo bảng

Mã:

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY(1,1),
Col1 INT,
Col2 VARCHAR(50)...
)

• Thêm cột vào bảng

Mã:

ALTER TABLE dbo.Tblyyy ADD ID_Col INT IDENTITY(1,1)

2. Cú pháp tổng quát của IDENTITY

• IDENTITY(seed, increment)
• Trong đó seed là giá trị khởi tạo và increment là giá trị tăng cho mỗi lần. Khi không chỉ định seed và increment thì các giá trị mặc định của chúng được dùng là seed = 1 và increment = 1. Ví dụ:

Giá trị khởi đầu = 1 và mỗi lần tăng 1

Mã:

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY...
)

Giá trị khởi đầu = 1000 và mỗi lần tăng 5

Mã:

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY(1000,5)...
)

Giá trị khởi đầu = -100 và mỗi lần giảm 2

Mã:

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY(-100,-2)...
)

Lưu ý là chuỗi giá trị có thể giảm dần (increment < 0) như ở ví dụ trên, nhưng tôi thấy điều này ít có giá trị thực tế và thậm chí còn dễ gây nhầm lẫn. Do đó trong bài này tôi luôn giả định là chuỗi luôn tăng.

3. Bạn không thể thay đổi một cột có sẵn để nó trở thành cột IDENTITY, hoặc bỏ thuộc tính IDENTITY của nó. Khi bạn edit bảng trong Management Studio và sửa một cột thành IDENTITY hoặc ngược lại, ở phía sau thực ra SQL Server xóa bảng và tạo một bảng mới.

4. SQL Server không duy trì tính liên tục của dãy số IDENTITY, khi bạn DELETE bản ghi thì khoảng trống sẽ xuất hiện. Ở lần INSERT sau đó một giá trị mới sẽ được dùng, thay vì giá trị bị xóa được dùng lại.

5. Bản thân thuộc tính IDENTITY không đảm bảo các giá trị trong cột là duy nhất. Ví dụ, bạn có đặt lại seed với một giá trị tùy ý (kể cả giá trị đã được dùng trước đó). Khi cần đảm bảo tính duy nhất, bạn cần thêm ràng buộc khóa chính hoặc khóa duy nhất cho cột.

6. Để khởi tạo lại giá trị seed, bạn dùng lệnh DBCC CHECKIDENT

7. Khi cần INSERT vào bảng, bạn có thể bỏ qua cột IDENTITY trong danh sách các cột, và giá trị của cột IDENTITY sẽ được tự động sinh ra (đó là mục đích chính khi tạo cột này)

Mã:

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY,
Col_1 INT,
Col_2 VARCHAR(50)
)
GO
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(15,'abc')
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(62,'def')
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(37,'ghi')

• Khi bạn muốn ghi đè lên giá trị tự sinh của cột IDENTITY, bạn phải đưa cột này vào danh sách các cột đồng thời trước đó phải đặt lại lựa chọn IDENTITY_INSERT là ON (mặc định là OFF)

Mã:

SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO dbo.MyTable(ID_Col,Col_1,Col_2) VALUES(100,42,'jkl')

• Một tình huống thường xảy ra là bạn INSERT vào bảng từ một bảng khác có cùng cấu trúc. Một cách lười biếng bạn có thể viết thế này:

Mã:

INSERT INTO dbo.Table1 SELECT * FROM dbo.Table2

• Khi bảng dbo.Table1 không có cột IDENTITY, câu lệnh trên thực hiện không có gì trở ngại. Nhưng nếu bảng dbo.Table1 có chứa cột IDENTITY, bạn sẽ nhận được thông báo lỗi sau:

Mã:

An explicit value for the identity column in table 'dbo.Table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

• Như vậy theo nội dung của thông báo lỗi, để thực hiện được câu lệnh INSERT trên bạn cần thực hiện hai việc: đặt IDENTITY_INSERT là ON cho bảng dbo.Table1, và liệt kê ra các cột:

Mã:

SET IDENTITY_INSERT dbo.Table1 ON
INSERT INTO dbo.Table1(ID_Col, Col_1,...)
SELECT ID_Col, Col_1,...
FROM dbo.Table2

8. Một bảng chỉ được phép có tối đa một cột IDENTITY. Bạn có thể tìm ra cột nào là IDENTITY bằng cách edit bảng trong Management Studio, hoặc bằng cách truy vấn view hệ thống SYS.IDENTITY_COLUMNS:

Mã:

SELECT *
FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_ID = OBJECT_ID('dbo.MyTable')

9. Để lấy giá trị IDENTITY cuối cùng vừa được INSERT, bạn dùng hàm SCOPE_IDENTITY(), gọi hàm này ở ngay dưới câu lệnh INSERT.

Mã:

INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(15,'abc')
SELECT SCOPE_IDENTITY()

Ngoài ra còn một vài hàm khác có cùng mục đích, nhưng khi đi vào chi tiết chúng có những khác biệt đáng kể. Trong phần lớn trường hợp, SCOPE_IDENTITY() là hàm an toàn nhất tránh được những rắc rối không cần thiết.

10. Với các tính năng như trên, cột IDENTITY sẽ gây ra khó khăn trong một số trường hợp. Nếu bạn muốn duy trì tính liên tục của dãy số (ví dụ, dùng lại các giá trị bị xóa trước đó), bạn phải tự thực hiện việc kiểm tra này trước mỗi lần INSERT. Việc xử lý cũng phức tạp hơn khi bảng nằm trong một replication với nhiều publisher cùng update dữ liệu về một subscriber. Đặc biệt với replication hai chiều (hai database update qua lại cho nhau) thì nó hoàn toàn bó tay. Lý do là vì giá trị IDENTITY kế tiếp chỉ được lưu cục bộ ở mỗi database, bảng này không biết giá trị kế tiếp của bảng ở bên database kia là bao nhiêu, cho nên khi replication xảy ra thì các giá trị INSERT vào mỗi bảng sẽ bị xung đột với nhau.