脚本之家 服务器常用软件
快捷导航
您的位置：首页 → 数据库 → MsSql → SQL Server identity自增

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

 更新时间：2020年10月20日 14:58:54   作者：码仙♥  
这篇文章主要介绍了SQL Server中identity(自增)的用法详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

一、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如何判断某个字段包含大写字母

    SQL SERVER如何判断某个字段包含大写字母

    本文将详细介绍SQL SERVER如何判断某个字段包含大写字母的方法，需要的朋友可以参考下
    2012-11-11
  • 使用SQL语句将相同名的多行字段内容拼接(方法详解)

    使用SQL语句将相同名的多行字段内容拼接(方法详解)

    这篇文章主要介绍了使用SQL语句将相同名的多行字段内容拼接起来,可以使用GROUP_CONCAT函数来实现相同名称的多行字段内容拼接，本文给大家介绍的非常详细，需要的朋友可以参考下
    2023-05-05
  • SQL server创建存储过程详细图文教程

    SQL server创建存储过程详细图文教程

    在SQL中存储过程类似于编程语言的函数,即当创建一个存储过程之后便可以在接下来任何类似的操作是直接调用存储过程,下面这篇文章主要给大家介绍了关于SQL server创建存储过程的相关资料,需要的朋友可以参考下
    2024-01-01
  • Sql Server中的非聚集索引详细介

    Sql Server中的非聚集索引详细介

    这篇文章主要介绍了Sql Server中的非聚集索引详细介,本文讲解了非聚集索引为什么能提速这么快、什么是RID、非聚集索引的运行原理等内容,需要的朋友可以参考下
    2015-02-02
  • sql server数据库高可用日志传送的方法

    sql server数据库高可用日志传送的方法

    SQL Server 使用日志传送，您可以自动将“主服务器”实例上“主数据库”内的事务日志备份发送到单独“辅助服务器”实例上的一个或多个“辅助数据库”，下面这篇文章主要给大家介绍了关于sql server数据库高可用日志传送的相关资料，需要的朋友可以参考下
    2018-05-05
  • MSSQL 数据库同步教程

    MSSQL 数据库同步教程

    MSSQL数据同步利用数据库复制技术实现数据同步更新(来自网络,也是非常完美的教程)
    2009-11-11
  • 图书管理系统的sqlserver数据库设计示例

    图书管理系统的sqlserver数据库设计示例

    这篇文章主要介绍了图书管理系统的sqlserver数据库设计示例,文中通过E_R图、数据字典、数据库脚本代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
    2020-08-08
  • MSSQL数据库迁移之用户名问题

    MSSQL数据库迁移之用户名问题

    数据库A有用户A，有一些用户A创建的表和SP。迁移时将data目录下的MDF和LDF移到新的MSSQL服务器上，通过附加数据库的功能将数据库恢复。
    2010-05-05
  • 使用SQL语句创建触发器的实例

    使用SQL语句创建触发器的实例

    触发器 是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。存储过程可以通过语句直接调用,而 触发器主要是通过事件进行触发而被执行的，这篇文章主要介绍了使用SQL语句创建触发器的实例,需要的朋友可以参考下
    2023-06-06
  • 常用SQL功能语句

    常用SQL功能语句

    SQLServer常用管理SQL语句
    2009-06-06

最新评论