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

作者:码仙♥ 时间:2024-01-24 22:33:50 

一、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 ('王五','女');

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

指定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 ('王五','女');

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

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 ('王五','女');

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

二、重新设置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 ('王五','女');

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

三、向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中identity(自增)的用法详解

补充知识: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(约束名)

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

来源:https://qinge.blog.csdn.net/article/details/81747159

标签:SQL,Server,identity,自增
0
投稿

猜你喜欢

  • python爬取各类文档方法归类汇总

    2022-02-22 05:45:47
  • ASP访问带多个参数的存储过程

    2008-10-14 16:45:00
  • PHP cron中的批处理

    2024-05-11 09:45:39
  • vue 自定义指令directives及其常用钩子函数说明

    2024-05-09 10:43:47
  • python图像平滑处理原理

    2023-07-07 00:21:11
  • 讲解MySQL数据库的数据类型和建库策略

    2008-12-17 14:39:00
  • 浅谈Python响应式类库RxPy

    2021-12-24 12:44:26
  • pytorch 获取层权重,对特定层注入hook, 提取中间层输出的方法

    2021-05-20 07:01:01
  • Python pymsql模块的使用

    2023-01-08 15:23:00
  • SQL 中 NULL值测试代码

    2024-01-19 04:03:32
  • jRaiser与jQuery的冲突问题

    2010-07-27 12:36:00
  • jupyter .ipynb转.py的实现操作

    2023-12-15 04:03:11
  • python中mechanize库的简单使用示例

    2021-05-22 14:32:12
  • tensorflow 获取所有variable或tensor的name示例

    2021-04-02 22:29:54
  • 数据库新手入门之MYSQL出错代码列表

    2008-12-23 15:22:00
  • mysql学习笔记之基础知识

    2024-01-13 08:27:16
  • Python实现邮件发送功能的方法详解

    2021-02-02 10:34:58
  • python区块链简易版交易实现示例

    2023-09-28 15:20:10
  • 对python中的os.getpid()和os.fork()函数详解

    2022-11-29 05:22:51
  • pytorch教程网络和损失函数的可视化代码示例

    2023-11-26 16:13:51
  • asp之家 网络编程 m.aspxhome.com