8 Replies Latest reply: Nov 19, 2012 1:05 AM by Mac_Freak_Rahul RSS

    primary key constraint ...

    Mac_Freak_Rahul
      Hi All,

      I have a table 'employees' on a schema 'hr' , now I have created a user 'rahul' on the same database and firing a query by logging with user 'rahul' which is :

      create table rahul.employees
      as (select * from hr.employees); -- ps : I have the privilege to read data of hr.employees

      Now the above query is working fine, the only issue is that employee_id is the primary key of hr.employees table and when I check rahul.employees table, the pruimary key constraint is missing, however the table structure and data is there as per my expectations.

      so just want to know how do I create a table with data using schema_name.table_name definition along with the primary key constraint.

      Regards
      Rahul kalra
        • 1. Re: primary key constraint ...
          jeneesh
          CTAS (Create Table as..) will copy only data, not Constraints
          use DBMS_METADATA to get the full DDL and dit it accodingly for your use
          select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual;
          Edited by: jeneesh on Nov 19, 2012 12:14 PM
          • 2. Re: primary key constraint ...
            stratmo
            Hi Rahul,

            a "create table as select" only creates the table structure "imitating" the source table. The rest could be done with functions like DBMS_METADATA.GET_DDL only.


            Regards

            stratmo
            • 3. Re: primary key constraint ...
              Mac_Freak_Rahul
              Thanks for the reply, but this query is returning me nothing, is there some privilege issue?

              Regards
              Rahul
              • 4. Re: primary key constraint ...
                user13325846
                If you want to create table and load data in one statement you can try this....

                create table rahul.employees(employee_id primary key,col1,col2) as (select employee_id,col1,col2 from hr.employees);
                • 5. Re: primary key constraint ...
                  jeneesh
                  Mac_Freak_Rahul wrote:
                  Thanks for the reply, but this query is returning me nothing, is there some privilege issue?

                  Regards
                  Rahul
                  Are you getting any error? if not, the result will be CLOB, just Copy it from your clint tool and paste it in a notepad..

                  between this is the table
                  CREATE TABLE "HR"."EMPLOYEES1" 
                     (     "EMPLOYEE_ID" NUMBER(6,0), 
                       "FIRST_NAME" VARCHAR2(20), 
                       "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
                       "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
                       "PHONE_NUMBER" VARCHAR2(20), 
                       "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
                       "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
                       "SALARY" NUMBER(8,2), 
                       "COMMISSION_PCT" NUMBER(2,2), 
                       "MANAGER_ID" NUMBER(6,0), 
                       "DEPARTMENT_ID" NUMBER(4,0), 
                        CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
                        CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
                    USING INDEX  ENABLE, 
                        CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
                    USING INDEX ENABLE, 
                        CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
                         REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE, 
                        CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
                         REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE, 
                        CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
                         REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
                     ) 
                  • 6. Re: primary key constraint ...
                    Mac_Freak_Rahul
                    thanks, yeah I am on toad and looking at result of this query gave me an impression that it returned, thank you so much :)

                    Regards
                    Rahul
                    • 7. Re: primary key constraint ...
                      stratmo
                      Hi Rahul,

                      have you selected directly? Is it showing a non-empty result?

                      Regards

                      stratmo
                      • 8. Re: primary key constraint ...
                        Mac_Freak_Rahul
                        yeah its a non empty result, ranging to multiple lines.

                        Regards
                        Rahul