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

Kennith

Which Oracle version are you using? – Nympholepsy

@Nympholepsy im using SQL Developer – Kennith

SQL Developer is an IDE. What Oracle database version are you using? Is it 11g ? Is it 12c ? Is it 18c ? From the Oracle SQL Developer Web page: Oracle SQL Developer is a free, integrated development environment. In other words, Oracle SQL Developer is not a database. – Nympholepsy

@Nympholepsy Currently im using 10g. – Kennith

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.

Granvillegranvillebarker

Is it also applicable when using create table statement in "as with" ? – Kennith

@Kennith Yes, it is. – Granvillegranvillebarker

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;

Suspensoid

Answer 3

You can create Suppliers table explicitly and if any column have primary key in companies table, then copy that structure from companies table.

Or

Create a same column defining primary key that you want and copy that column from companies table!

Noontime