表的插入、更新、删除、合并操作_2_插入含自增列的记录

                           插入含自增列的记录

需求描述

需求：向dept表中插入部门编号为50,部门名称为Production,部门位置为Shanghai的数据.

解决方法:这里通过INSERT INTO TableName VALUES (…),这里表dept的deptno字段定义的是自增.

注: 数据库数据集SQL脚本详见如下链接地址

​​员工表结构和数据初始化SQL脚本​​

SQL代码

-- MySql:
INSERT INTO dept VALUES (50,'Production','Shanghai');

/*
Mysql虽然在字段定义时约束了自增,但是在插入自增数据时依然可以显示的插入,只要自增字段的值不和已有的数据重复即可.

如果插入的重复的自增字段数据,则会有类似如下的报错:

Duplicate entry '50' for key 'PRIMARY'
*/


-- SQL Server:
INSERT INTO dept VALUES (50,'Production','Shanghai');

/*
消息 8101，级别 16，状态 1，第 2 行

仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'dept'中的标识列指定显式值。

问题原因:

dept表的deptno字段设置了自增模式,而默认默认情况下对自增字段的插入是数据库自己维护的,所以当用户手动指定时则会抛出该异常.

*/
create table dept(

    deptno int IDENTITY(1,1) NOT NULL,

    dname varchar(15),

    loc varchar(50),

primary key(deptno)

);

/*
解决该问题有3种办法:

1 修改表结构的定义,去掉表的自增属性。不建议。
2 只插入除自增外的其他字段,让数据库自行维护自增字段。建议。
3 通过打开表的IDENTITY_INSERT开关,显示插入指定的自增字段。建议.
其中第三种方法的实现代码见下:
*/

SET IDENTITY_INSERT dept ON;
GO
INSERT INTO dept(deptno,dname,loc) VALUES (50,'Production','Shanghai');
SET IDENTITY_INSERT dept OFF;
GO

-- Oracle:

/*
Oracle里对于自增字段的维护麻烦点,因为它没有对应的关键字.不过我们可以通过内置的数据库对象sequence来实现.具体实现见下:
*/


create sequence dept_autoinc
     minvalue 50
     maxvalue 9999999999999999999999999999
     start with 50
     increment by 10
     nocache;

INSERT INTO dept VALUES(dept_autoinc.nextval,'Production','Shanghai');

执行结果

注：对应oracle里的执行结果。

延展阅读

Oracle 12c中已经有IDENTITY关键字来维护自增字段

CREATE TABLE t1 (
    c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    c2 VARCHAR2(10)
    );
    
    INSERT INTO t1(c2) VALUES(10);
    INSERT INTO t1(c2) VALUES(20);