Create table as select statement primary key in oracle

Question

Is it possible to specify which is the primary key on creating table as select statement? My aim is to include the declaration of primary key on the create table not modifying the table after the creation.

CREATE TABLE suppliers
AS (SELECT company_id, address, city, state, zip
  FROM companies
  WHERE company_id < 5000);

Answer 1

Yes, it's possible. You would need to specify columns explicitly:

CREATE TABLE suppliers (
    company_id primary key, 
    address, 
    city, 
    state, 
    zip
) 
AS 
  SELECT company_id, address, city, state, zip
    FROM companies
    WHERE company_id < 5000;

Here is a demo

Note: in this case primary key constraint will be given a system-generated name. If you want it to have a custom name you'd have to execute alter table suppliers add constraint <<custom constraint name>> primary key(<<primary_key_column_name>>) after executing(without primary key specified) CREATE TABLE suppliers.. DDL statement.

Answer 2

Yes, it's possible.You can try referring below example.

create table student (rollno ,student_name,score , constraint pk_student primary key(rollno,student_name))
as
    select empno,ename,sal 
    from emp;