oracle错误(二) ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")的解决方案

最新推荐文章于 2024-04-29 09:55:17 发布

小米加大炮

最新推荐文章于 2024-04-29 09:55:17 发布

阅读量9.9w

点赞数 13

分类专栏：数据库-oracle 文章标签： oracle

本文链接：https://blog.csdn.net/zengdeqing2012/article/details/46376493

版权

数据库-oracle 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

之前都是mysql数据库，现在因为项目需要开始接触oracle数据库,新手上路,难免磕磕碰碰,把自己遇到的问题记录下来,方便查询！

一建表脚本:

DROP TABLE TEMP_TEST_TABLE;  
CREATE TABLE TEMP_TEST_TABLE
(
  id              NUMBER NOT NULL,
  author          VARCHAR2(200),
  publishing      VARCHAR2(200),
  publish_time    TIMESTAMP(6),
  word_number     VARCHAR2(15),
  which_edition   VARCHAR2(15),
  total_page      VARCHAR2(15),
  print_time      TIMESTAMP(6),
  print_number    VARCHAR2(15),
  isbn            VARCHAR2(25),
  author_summary  VARCHAR2(250),
  catalogue       VARCHAR2(250)
)

--指定表空间(可以不指定)
tablespace DSW
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
-- Add comments to the table 
comment on table TEMP_TEST_TABLE is '学习测试表';
-- Add comments to the columns 
comment on column TEMP_TEST_TABLE.id is '图书ID';
comment on column TEMP_TEST_TABLE.author is '图书作者';
comment on column TEMP_TEST_TABLE.publishing is '出版社';
comment on column TEMP_TEST_TABLE.publish_time is '出版时间';
comment on column TEMP_TEST_TABLE.word_number is '字数';
comment on column TEMP_TEST_TABLE.which_edition is '版本号码';
comment on column TEMP_TEST_TABLE.total_page is '总页数';
comment on column TEMP_TEST_TABLE.print_time is '印刷时间';
comment on column TEMP_TEST_TABLE.print_number is '印刷次数';
comment on column TEMP_TEST_TABLE.isbn is '版本号';
comment on column TEMP_TEST_TABLE.author_summary is '作者简介';
comment on column TEMP_TEST_TABLE.catalogue is '图书描述';

-- Create/Recreate primary, unique and foreign key constraints 
alter table TEMP_TEST_TABLE
  add constraint PK_TEMP_TEST_TABLE primary key (ID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

结果项目成功启动后,调用接口时,接口逻辑没有问题,作新增操作的时候,一直在报错;

信息如下:

2015-06-05 10:32:43,996 DEBUG [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] - Translating SQLException with SQL state '23000', error code '1400', message [ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")
]; SQL was [] for task [
### Error updating database.  Cause: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

]
2015-06-05 10:32:43,996 DEBUG [org.mybatis.spring.SqlSessionUtils] - Closing no transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@55450411]
2015-06-05 10:32:43,996 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

; SQL []; ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")
; nested exception is java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:249)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
	at com.sun.proxy.$Proxy8.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
	at com.sun.proxy.$Proxy36.insertTempTestTable(Unknown Source)
	at xyx.dsw.business.admin.quotationwish.TempTestTableInfo.onAdd(TempTestTableInfo.java:284)
	at xyx.dsw.business.base.BaseBusiness.add(BaseBusiness.java:67)
	at cn.xyx.dsw.admin.quotationwish.controller.TempTestTableController.saveJson(TempTestTableController.java:140)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2927)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:94)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)
	at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
	at com.sun.proxy.$Proxy20.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
	at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:32)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
	... 44 more
2015-06-05 10:32:43,997 ERROR [cn.xyx.dsw.admin.quotationwish.controller.TempTestTableController] - add save
org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

; SQL []; ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")
; nested exception is java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:249)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
	at com.sun.proxy.$Proxy8.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
	at com.sun.proxy.$Proxy36.insertTempTestTable(Unknown Source)
	at xyx.dsw.business.admin.quotationwish.TempTestTableInfo.onAdd(TempTestTableInfo.java:284)
	at xyx.dsw.business.base.BaseBusiness.add(BaseBusiness.java:67)
	at cn.xyx.dsw.admin.quotationwish.controller.TempTestTableController.saveJson(TempTestTableController.java:140)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: java.sql.SQLException: ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2927)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:94)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)
	at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
	at com.sun.proxy.$Proxy20.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
	at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:32)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
	... 44 more

仔细检查了好几遍都没有问题,结果不是代码的问题;是oracle数据库中TEMP_TEST_TABLE表字段ID是自增的,没有创建序列和触发器的缘故;

因此为该表加上序列和触发器,即可

二创建序列脚本:

-- Create sequence 
create sequence TEMP_TEST_TABLE_SEQ
minvalue 1    -- 最小值=1
maxvalue 999999999999999999999999999  -- 指定最大值 
-- 或nomaxvalue      -- 没有最大值
-- NOCYCLE;      -- 不循环
start with 1   -- 从1开始
increment by 1  -- 每次递增1
cache 20;

在PLSQL Developer中创建：

三创建触发器脚本:

</pre><pre name="code" class="html">--触发器
create or replace trigger TEMP_TEST_TABLE_TRI
  before insert on TEMP_TEST_TABLE  
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
begin
    SELECT TEMP_TEST_TABLE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
end;

四完整的oracle建表脚本:

-- Create table

DROP TABLE TEMP_TEST_TABLE;  
CREATE TABLE TEMP_TEST_TABLE
(
  id              NUMBER NOT NULL,
  author          VARCHAR2(200),
  publishing      VARCHAR2(200),
  publish_time    TIMESTAMP(6),
  word_number     VARCHAR2(15),
  which_edition   VARCHAR2(15),
  total_page      VARCHAR2(15),
  print_time      TIMESTAMP(6),
  print_number    VARCHAR2(15),
  isbn            VARCHAR2(25),
  author_summary  VARCHAR2(250),
  catalogue       VARCHAR2(250)
)

--指定表空间(可以不指定)
tablespace DSW
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
-- Add comments to the table 
comment on table TEMP_TEST_TABLE is '学习测试表';
-- Add comments to the columns 
comment on column TEMP_TEST_TABLE.id is '图书ID';
comment on column TEMP_TEST_TABLE.author is '图书作者';
comment on column TEMP_TEST_TABLE.publishing is '出版社';
comment on column TEMP_TEST_TABLE.publish_time is '出版时间';
comment on column TEMP_TEST_TABLE.word_number is '字数';
comment on column TEMP_TEST_TABLE.which_edition is '版本号码';
comment on column TEMP_TEST_TABLE.total_page is '总页数';
comment on column TEMP_TEST_TABLE.print_time is '印刷时间';
comment on column TEMP_TEST_TABLE.print_number is '印刷次数';
comment on column TEMP_TEST_TABLE.isbn is '版本号';
comment on column TEMP_TEST_TABLE.author_summary is '作者简介';
comment on column TEMP_TEST_TABLE.catalogue is '图书描述';

-- Create/Recreate primary, unique and foreign key constraints 
alter table TEMP_TEST_TABLE
  add constraint PK_TEMP_TEST_TABLE primary key (ID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
-- Create sequence 
create sequence TEMP_TEST_TABLE_SEQ
minvalue 1    -- 最小值=1
maxvalue 999999999999999999999999999  -- 指定最大值 
-- 或nomaxvalue      -- 没有最大值
-- NOCYCLE;      -- 不循环
start with 1   -- 从1开始
increment by 1  -- 每次递增1
cache 20;

--触发器
create or replace trigger TEMP_TEST_TABLE_TRI
  before insert on TEMP_TEST_TABLE  
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
begin
    SELECT TEMP_TEST_TABLE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
end;

小米加大炮

关注

13
点赞
踩
52

收藏

觉得还不错? 一键收藏
5
评论
oracle错误(二) ORA-01400: 无法将 NULL 插入 ("SL"."TEMP_TEST_TABLE"."ID")的解决方案

之前都是mysql数据库，现在因为项目需要开始接触oracle数据库,新手上路,难免磕磕碰碰,把自己遇到的问题记录下来,方便查询！一建表脚本:DROP TABLE TEMP_TEST_TABLE; CREATE TABLE TEMP_TEST_TABLE( id NUMBER NOT NULL, author VARCHAR2(2
复制链接

扫一扫

专栏目录