SQL Server中identity(自增)的用法詳解

一、identity的基本用法

1.含義

identity表示該字段的值會自動更新，不需要我們維護，通常情況下我們不可以直接給identity修飾的字符賦值，否則編譯時會報錯

2.語法

列名 數(shù)據(jù)類型 約束 identity(m,n)

m表示的是初始值，n表示的是每次自動增加的值

如果m和n的值都沒有指定，默認為（1,1）

要么同時指定m和n的值，要么m和n都不指定，不能只寫其中一個值，不然會出錯

3.實例演示

不指定m和n的值

create table student1
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student1(sname,ssex) values ('張三','男');
insert into student1 values ('李四','女');--可以省略列名
insert into student1 values ('王五','女');

指定m和n的值

create table student2
(
 sid int primary key identity(20,5),
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student2(sname,ssex) values ('張三','男');
insert into student2 values ('李四','女');--可以省略列名
insert into student2 values ('王五','女');

4.刪除一條記錄接著插入

把sid為2的記錄刪除，繼續(xù)插入，新插入的記錄的sid不是2，而是3

create table student3
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student3(sname,ssex) values ('張三','男');
insert into student3 values ('李四','女');
delete from student3 where sid=2;--把sid為2的記錄刪除
insert into student3 values ('王五','女');

二、重新設置identity的值

1.語法

dbcc checkident(表名,reseed,n);

n+1表示的是表中identity字段的初始值（n的值可以為0）

也就是說：如果插入的是id為2的記錄，則n的值是1

2.實例演示

create table student4
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student4(sname,ssex) values ('張三','男');
insert into student4 values ('李四','女');
delete from student4 where sid=2;--把sid為2的記錄刪除
dbcc checkident('student4',reseed,1);--把student4表中identity字段的初始值重新設置為1
insert into student4 values ('王五','女');

三、向identity字段插入數(shù)據(jù)

1.語法

set identity_insert 表名 on;

insert into 表名(列名1,列名2,列名3,列名4) values (數(shù)據(jù)1,數(shù)據(jù)2,數(shù)據(jù)3,數(shù)據(jù)4);

set identity_insert 表名 off;

注意：插入數(shù)據(jù)時必須得指定identity修飾的字段的名字

2.實例演示

create table student5
(
 sid int primary key identity(20,5),
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student5(sname,ssex) values ('張三','男');
insert into student5 values ('李四','女');
insert into student5 values ('王五','女');
set identity_insert student5 on;
/*
insert into student5 values ('黑六','男');--error
insert into student5 values (21,'黑六','男');--error
*/
insert into student5(sid,sname,ssex) values (21,'黑六','男');
set identity_insert student5 off;
/*
insert into student5 values (22,'趙七','女');--error
insert into student5(sid,sname,ssex) values (22,'趙七','女');--error
*/
insert into student5 values ('趙七','女');

補充知識：SQL Server 添加與刪除主鍵約束

PRIMARY KEY 約束唯一標識數(shù)據(jù)庫表中的每條記錄。主鍵必須包含唯一的值。主鍵列不能包含 NULL 值。每個表都應該有一個主鍵，并且每個表只能有一個主鍵。

主鍵約束操作包含了添加約束和刪除約束，修改約束其實是添加約束或者刪除約束。

添加主鍵約束比較 簡單，但是刪除一個沒有約束名的主鍵約束則比較復雜，如果不是很不了解SQL Server的話則比很難實現(xiàn)該功能。

主鍵約束操作基本語句

如果是在新建表的時候添加主鍵約束比較簡單，直接在列名后加入primary key即可，標準的添加主鍵約束的SQL腳本如下：

create table 表名 (  字段名1 int not null, ...,
  [constraint 約束名] primary key (字段名1, …)
)

其中約束名可選（建議選擇，否則系統(tǒng)會分配隨機臨時名稱）。這種方法可以任意添加多個或一個主鍵約束。

對已有表添加主鍵約束，與上面的腳本類似，如下：

alter table 表名 [add constraint 約束名] primary key(字段名1,... )

其中約束名與上相同，可選，建議指定。

SQL Server刪除主鍵約束與MySQL不同，需要主鍵的約束名稱才能刪除，SQL Server標準的刪除主鍵的腳本如下：

ALTER TABLE 表名DROP CONSTRAINT 約束名

當然該語句只能刪除已知約束名的主鍵約束。

刪除未知主鍵約束名的約束

根據(jù)之前的語句判斷，這個約束名如果之前已經(jīng)指定了那還好，否則此次就沒有辦法刪除了，重點在于如何獲取表中的主鍵約束名，既然可以根據(jù)約束名，那么就說明約束名是存儲在數(shù)據(jù)庫中的，當然是存儲在系統(tǒng)表中的，運行下SELECT * FROM SYS.OBJECTS 語句看看查詢結果，數(shù)據(jù)庫中所有的約束都 在里面了，name項以PK大頭的都是主鍵約束，看看它的type_desc是不是PRIMARY_KEY_CONSTRAINT或者type是不是 PK？（當然指定的名稱沒使用PK前綴另當別論）。

既然已知表名，而且也知道主鍵約束存儲位置，關鍵就是二者如何關聯(lián)起來，使得使用表名就可以查詢到主鍵 約束名稱，這其中溝通的橋梁便是parent_object_id。

這里給出的只是object_id，那么又如何找出表的object_id呢？再仔細 看看剛才的搜索結果，原來數(shù)據(jù)庫中的相關信息全在里面，表，函數(shù)等都在里面，而且有自身的object_id。

接下來整合下上面的思路，根據(jù)表名查詢表ID，根據(jù)表ID找到主鍵約束名稱，采用級聯(lián)方式查詢就可以查詢出來?？梢詷嬙烊缦碌牟樵冋Z句：

SELECT NAME from SYS.OBJECTS WHERE TYPE_DESC ='PRIMARY_KEY_CONSTRAINT' AND
PARENT_OBJECT_ID = (SELECT OBJECT_ID
FROM SYS.OBJECTS WITH(NOLOCK) WHERE NAME = '表名')

然后NAME就是我們需要的約束名稱了。然后在構造一個上面的刪除約束的SQL語句執(zhí)行就可以了，典型的SQL如下:

ALTER TABLE 表名 DROP CONSTRAINT NAME(約束名)

其他約束都可以采用這種方法進行刪除。

以上這篇SQL Server中identity(自增)的用法詳解就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持腳本之家。