CODEWITHSUNDEEP
oracle-databaseplsqlplsqldevelopersql-scripts
absolute
absolute

Reputation: 63

Create table as select statement primary key in oracle

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

Upvotes: 5

Views: 8344

Answers (3)

Praveen Deshmukh
Praveen Deshmukh

Reputation: 21

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;

Upvotes: 2

Prem Jogi
Prem Jogi

Reputation: 57

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!

Upvotes: -1

Nick Krasnov
Nick Krasnov

Reputation: 27261

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.

Upvotes: 7

Related Questions