0

How do I insert (or generate and insert) a primary key when I use the SELECT DISTINCT query?

If I have a table made like this:

CREATE TABLE testUser(
    username VARCHAR2(50 CHAR),
    email VARCHAR2(50 CHAR)
)

And I had some table customer_orders with this data I could quickly populate this using:

INSERT INTO testUser(username, email)
SELECT DISTINCT user_name, user_email
FROM customer_info;

However if instead I have this table:

CREATE TABLE users(
   id NUMBER,
   user_name VARCHAR2(50 CHAR),
   user_email VARCHAR2(50 CHAR)
)

and try to use the same method (the SELECT DISTINCT INSERT method) I'll get an error like ORA-01400: cannot insert NULL into ("MYDB"."USERS"."ID").

This makes sense since the primary key is null, how can I autogenerate a primary key 1-n where n is the COUNT(SELECT DISTINCT user_name)?

2

Let the database generate it for you (identity column, 12c feature):

CREATE TABLE users(
   id NUMBER generated always as identity, -- <= HERE
   user_name VARCHAR2(50 CHAR),
   user_email VARCHAR2(50 CHAR)
);

If you are still on a version below 12c, then you can simply use rownum:

INSERT INTO testUser(id, username, email)
SELECT rownum, sq.* from
(select DISTINCT user_name, user_email
FROM customer_info) sq;

Or a sequence:

create sequence s1;
INSERT INTO testUser(id, username, email)
SELECT s1.nextval, sq.* from
(select DISTINCT user_name, user_email
FROM customer_info) sq;

Or a trigger, as described here: https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle

  • You need to add ON NULL to the IDENTITY clause so that APEX Automatic DML Process will work. – Michael Kutz Jun 1 '18 at 11:04
  • @MichaelKutz as in id number generated as identity on null? – ryanmattscott Jun 1 '18 at 11:45
  • it is actually id number generated by default ON NULL as identity. The Application Express [APEX] process sends a NULL value for new rows. The ON NULL flag allows the IDENTITY value to be set even when a NULL value is part of the INSERT statement. Try INSERT INTO T (id) VALUES (null) on a table with the ON NULL flag and again without it. – Michael Kutz Jun 1 '18 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.