OracleDB – Generate and Insert Primary Key Using SELECT DISTINCT

database-designdistinctinsertoracleselect

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)?

Best Answer

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

Related Question