2

I am using Oracle 12c, and I generated a table with an identity primary key:

 create table t
    ( x int
        generated as identity
            primary key,
      y varchar2(30)
    )

When I run an insert statement like this:

insert into t (y) 
 select 'a' from dual

everything looks fine. But when I want to use a union in the select statement it gives an error:

insert into t (y) 
 select 'a' from dual
 union
 select 'b' from dual

Error report:

SQL Error: ORA-01400: cannot insert NULL into ("REPORT"."T"."X")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.

vldmrrdjcc
  • 2,082
  • 5
  • 22
  • 41
  • 2
    I don't have Oracle 12c, but you might try: `insert into t (y) select from ( select 'a' from dual union select 'b' from dual)` It's just a shot in the dark... – Jodevan Mar 15 '16 at 16:25
  • 1
    There are some options to how Identity columns are created. I don't have a 12c in my dev environment at the moment, but try to create the column x int GENERATED BY DEFAULT ON NULL AS IDENTITY primary key. – Michael Broughton Mar 15 '16 at 16:29
  • Extra strange: Oracle 12c, without an `ALWAYS` or `BY DEFAULT` option it defaults to [`ALWAYS`](https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAHJHJC). I can't think of a good reason why the first insert works but the second doesn't. – Mr. Llama Mar 15 '16 at 16:59

0 Answers0