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

数据库 2024/11/5 佚名

3 2 1

一、identity的基本用法

1.含义

identity表示该字段的值会自动更新，不需要我们维护，通常情况下我们不可以直接给identity修饰的字符赋值，否则编译时会报错

2.语法

列名数据类型约束 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的记录删除，继续插入，新插入的记录的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字段插入数据

1.语法

set identity_insert 表名 on;

insert into 表名(列名1,列名2,列名3,列名4) values (数据1,数据2,数据3,数据4);

set identity_insert 表名 off;

注意：插入数据时必须得指定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 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。

主键约束操作包含了添加约束和删除约束，修改约束其实是添加约束或者删除约束。

添加主键约束比较简单，但是删除一个没有约束名的主键约束则比较复杂，如果不是很不了解SQL Server的话则比很难实现该功能。

主键约束操作基本语句

如果是在新建表的时候添加主键约束比较简单，直接在列名后加入primary key即可，标准的添加主键约束的SQL脚本如下：

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

其中约束名可选（建议选择，否则系统会分配随机临时名称）。这种方法可以任意添加多个或一个主键约束。

对已有表添加主键约束，与上面的脚本类似，如下：

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

其中约束名与上相同，可选，建议指定。

SQL Server删除主键约束与MySQL不同，需要主键的约束名称才能删除，SQL Server标准的删除主键的脚本如下：

ALTER TABLE 表名DROP CONSTRAINT 约束名

当然该语句只能删除已知约束名的主键约束。

删除未知主键约束名的约束

根据之前的语句判断，这个约束名如果之前已经指定了那还好，否则此次就没有办法删除了，重点在于如何获取表中的主键约束名，既然可以根据约束名，那么就说明约束名是存储在数据库中的，当然是存储在系统表中的，运行下SELECT * FROM SYS.OBJECTS 语句看看查询结果，数据库中所有的约束都在里面了，name项以PK大头的都是主键约束，看看它的type_desc是不是PRIMARY_KEY_CONSTRAINT或者type是不是 PK？（当然指定的名称没使用PK前缀另当别论）。

既然已知表名，而且也知道主键约束存储位置，关键就是二者如何关联起来，使得使用表名就可以查询到主键约束名称，这其中沟通的桥梁便是parent_object_id。

这里给出的只是object_id，那么又如何找出表的object_id呢？再仔细看看刚才的搜索结果，原来数据库中的相关信息全在里面，表，函数等都在里面，而且有自身的object_id。

接下来整合下上面的思路，根据表名查询表ID，根据表ID找到主键约束名称，采用级联方式查询就可以查询出来。可以构造如下的查询语句：

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语句执行就可以了，典型的SQL如下:

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

其他约束都可以采用这种方法进行删除。

以上这篇SQL Server中identity(自增)的用法详解就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。

SQL,Server,identity自增

标签：

SQL,Server,identity自增

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

白云城资源网 Copyright www.dyhadc.com

评论“SQL Server中identity(自增)的用法详解”

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

暂无“SQL Server中identity(自增)的用法详解”评论...

www.dyhadc.com 白云城资源网

129,905影音资源

244,626技术资源

111,817软件资源

578,645站长资源

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

一、identity的基本用法

二、重新设置identity的值

三、向identity字段插入数据

SQL,Server,identity自增

SQL Server 使用join all优化 or 查询速度

SQL删除语句DROP、TRUNCATE、 DELETE 的区别

评论“SQL Server中identity(自增)的用法详解”

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

更新日志

友情链接